Sfrgttk

Approximately how much travel time was saved by the opening of the Suez Canal in 1869?

Is it possible to do 50 km distance without any previous training?

How do I deal with an unproductive colleague in a small company?

Why can't we play rap on piano?

High voltage LED indicator 40-1000 VDC without additional power supply

Alternative to sending password over mail?

I'm flying to France today and my passport expires in less than 2 months

LaTeX: Why are digits allowed in environments, but forbidden in commands?

Did Shadowfax go to Valinor?

How can I make my BBEG immortal short of making them a Lich or Vampire?

What defenses are there against being summoned by the Gate spell?

A case of the sniffles

Can a vampire attack twice with their claws using Multiattack?

Cross compiling for RPi - error while loading shared libraries

Was any UN Security Council vote triple-vetoed?

How old can references or sources in a thesis be?

How to determine what difficulty is right for the game?

Can an x86 CPU running in real mode be considered to be basically an 8086 CPU?

What is the word for reserving something for yourself before others do?

How is the claim "I am in New York only if I am in America" the same as "If I am in New York, then I am in America?

Is it unprofessional to ask if a job posting on GlassDoor is real?

Do infinite dimensional systems make sense?

What's that red-plus icon near a text?

Horror movie about a virus at the prom; beginning and end are stylized as a cartoon

Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library

Using PL/R for clustering geometriesMultiple databases with medium tables or Single database with huge tablesHow to correctly use PostgreSQL to limit multiple and/or concurrent executions of a taskSelect ONE most recent post for each authorHow exactly the row visibility is determined?Use large set of data in multiple databasesPostgreSQL: import several .csv files through sql copy statementHow to pass a set or unnamed relation into a PostgreSQL function?Finding and storing groups of rows PKPostgreSQL most efficient way to reference multiple tables

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

2

I am writing a PostgreSQL extension using the C language. I would like to do the following tasks:

1. Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in its table.


2. Process this set and return other set of rowids


3. As a result, I want to access the tuples pointed by the returned rowids

For instance, I have a table with the rowids => {1, 2, ..., 10000}. Then, I have processed my algorithm, and as result I have the following rowids => {3, 100, 8394, 12}

However, I have some doubts:

1. What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?




2. How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

postgresql performance

share|improve this question

edited Jan 25 '18 at 15:30

Evan Carroll

33.4k1076232

asked Mar 14 '16 at 23:49

Anderson CarnielAnderson Carniel

1112

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well, I will use SPI.
  
  – Anderson Carniel
  Mar 18 '16 at 16:51
  

  
  
  
  

add a comment | 

2

I am writing a PostgreSQL extension using the C language. I would like to do the following tasks:

1. Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in its table.


2. Process this set and return other set of rowids


3. As a result, I want to access the tuples pointed by the returned rowids

For instance, I have a table with the rowids => {1, 2, ..., 10000}. Then, I have processed my algorithm, and as result I have the following rowids => {3, 100, 8394, 12}

However, I have some doubts:

1. What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?




2. How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

postgresql performance

share|improve this question

edited Jan 25 '18 at 15:30

Evan Carroll

33.4k1076232

asked Mar 14 '16 at 23:49

Anderson CarnielAnderson Carniel

1112

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well, I will use SPI.
  
  – Anderson Carniel
  Mar 18 '16 at 16:51
  

  
  
  
  

add a comment | 

I am writing a PostgreSQL extension using the C language. I would like to do the following tasks:

1. Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in its table.


2. Process this set and return other set of rowids


3. As a result, I want to access the tuples pointed by the returned rowids

For instance, I have a table with the rowids => {1, 2, ..., 10000}. Then, I have processed my algorithm, and as result I have the following rowids => {3, 100, 8394, 12}

However, I have some doubts:

1. What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?




2. How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

postgresql performance

share|improve this question

edited Jan 25 '18 at 15:30

Evan Carroll

33.4k1076232

asked Mar 14 '16 at 23:49

Anderson CarnielAnderson Carniel

1112

share|improve this question

edited Jan 25 '18 at 15:30

Evan Carroll

33.4k1076232

asked Mar 14 '16 at 23:49

Anderson CarnielAnderson Carniel

1112

share|improve this question

edited Jan 25 '18 at 15:30

Evan Carroll

33.4k1076232

asked Mar 14 '16 at 23:49

Anderson CarnielAnderson Carniel

1112

edited Jan 25 '18 at 15:30

Evan Carroll

33.4k1076232

edited Jan 25 '18 at 15:30

Evan Carroll

33.4k1076232

asked Mar 14 '16 at 23:49

Anderson CarnielAnderson Carniel

1112

asked Mar 14 '16 at 23:49

Anderson CarnielAnderson Carniel

1112

1 Answer
1

active

oldest

votes

0

These are two different questions and you shouldn't ask compound questions on StackExchange.

1. 
   

   What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?

   
   
   

   Create your own ID. PostgreSQL doesn't create you IDs for userspace unless you explicitly request it: which you can do with GENERATED BY DEFAULT AS IDENTITY (or serial in pre-PostgreSQL 10)

   
   


2. 
   

   How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

   
   
   

   You don't. PostgreSQL isn't intentionally slow if there was a faster and safer way to do something by default, it would be done. Without knowing what compromises you're willing to make that the server isn't, there is no way to answer this question.

   
   
   

   If you're not willing to make additionally compromises, it's not likely you could safe more time without a total rewrite, or merely preplanning the UPDATE.

   
   
   

   An extension doesn't magically make everything faster.

   
   

share|improve this answer

answered Jan 25 '18 at 15:35

Evan CarrollEvan Carroll

33.4k1076232

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f132234%2faccessing-a-row-directly-at-a-table-in-postgresql-by-using-the-postgresqls-inte%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

These are two different questions and you shouldn't ask compound questions on StackExchange.

1. 
   

   What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?

   
   
   

   Create your own ID. PostgreSQL doesn't create you IDs for userspace unless you explicitly request it: which you can do with GENERATED BY DEFAULT AS IDENTITY (or serial in pre-PostgreSQL 10)

   
   


2. 
   

   How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

   
   
   

   You don't. PostgreSQL isn't intentionally slow if there was a faster and safer way to do something by default, it would be done. Without knowing what compromises you're willing to make that the server isn't, there is no way to answer this question.

   
   
   

   If you're not willing to make additionally compromises, it's not likely you could safe more time without a total rewrite, or merely preplanning the UPDATE.

   
   
   

   An extension doesn't magically make everything faster.

   
   

share|improve this answer

answered Jan 25 '18 at 15:35

Evan CarrollEvan Carroll

33.4k1076232

add a comment | 

0

These are two different questions and you shouldn't ask compound questions on StackExchange.

1. 
   

   What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?

   
   
   

   Create your own ID. PostgreSQL doesn't create you IDs for userspace unless you explicitly request it: which you can do with GENERATED BY DEFAULT AS IDENTITY (or serial in pre-PostgreSQL 10)

   
   


2. 
   

   How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

   
   
   

   You don't. PostgreSQL isn't intentionally slow if there was a faster and safer way to do something by default, it would be done. Without knowing what compromises you're willing to make that the server isn't, there is no way to answer this question.

   
   
   

   If you're not willing to make additionally compromises, it's not likely you could safe more time without a total rewrite, or merely preplanning the UPDATE.

   
   
   

   An extension doesn't magically make everything faster.

   
   

share|improve this answer

answered Jan 25 '18 at 15:35

Evan CarrollEvan Carroll

33.4k1076232

add a comment | 

These are two different questions and you shouldn't ask compound questions on StackExchange.

1. 
   

   What tuple identifier would be the best to be stored in the task 1? According to the documentation, the ctid value is not recommended to be used since it can change after a VACCUM. But, which value could I store? Any recommendation?

   
   
   

   Create your own ID. PostgreSQL doesn't create you IDs for userspace unless you explicitly request it: which you can do with GENERATED BY DEFAULT AS IDENTITY (or serial in pre-PostgreSQL 10)

   
   


2. 
   

   How to directly access the set of tuples that are pointed by the returned rowids of task 2? One solution would be to use SQL query. However, is it efficient? I mean, I am writing a extension of PostgreSQL, therefore, I have access to its internal library. Is there a direct way to do it instead of use sql queries? Is it a good and efficient solution use SQL queries with SPI?

   
   
   

   You don't. PostgreSQL isn't intentionally slow if there was a faster and safer way to do something by default, it would be done. Without knowing what compromises you're willing to make that the server isn't, there is no way to answer this question.

   
   
   

   If you're not willing to make additionally compromises, it's not likely you could safe more time without a total rewrite, or merely preplanning the UPDATE.

   
   
   

   An extension doesn't magically make everything faster.

   
   

share|improve this answer

answered Jan 25 '18 at 15:35

Evan CarrollEvan Carroll

33.4k1076232

share|improve this answer

answered Jan 25 '18 at 15:35

Evan CarrollEvan Carroll

33.4k1076232

answered Jan 25 '18 at 15:35

Evan CarrollEvan Carroll

33.4k1076232

answered Jan 25 '18 at 15:35

Evan CarrollEvan Carroll

33.4k1076232