Re: I think that my data is saved correctly, but when printing again, other data appears

[Jony Cohen]

Hi,
Worked on something similar a few years back, have a look - it might give
you a few pointers :)
It's on similar lines to what you are looking at (I kept it updated up to
PG version 9.4/9.5)
https://github.com/cohenjo/pg_idx_advisor

My main focus was to add support for more index types: partial, functional,
CTE, composites etc...
(I also had to keep the original context to restore it - so I think you
will find a sample to what you are looking for... )

there's also https://github.com/HypoPG/hypopg which is actively maintained.
I think you will find code samples even up to the latest versions here...

Regards,
 - Jony

On Sat, Oct 26, 2019 at 4:06 AM Yessica Brinkmann <
yessica.brinkm...@gmail.com> wrote:

> Good evening, sorry for the delay in answering. I have a part-time job and
> I was at it.
> I understand what you tell me about the "data" value is just a pointer
> into the tupdesc associated with the SPI result, and that disappears the
> moment I do SPI_finish ().
> What I do not understand well is how to use CurrentMemoryContext and
> MemoryContextStrdup, since there are not many examples of using them on the
> Internet (most are only definitions) and it is the first time I have heard
> of this, although I already understand this part now of contexts, because I
> was reading on the subject.
> Could you please give me an example of use?
> Best Regards,
> Yessica Brinkmann
>
> El vie., 25 oct. 2019 a las 12:24, Yessica Brinkmann (<
> yessica.brinkm...@gmail.com>) escribió:
>
>> Thank you so much for your answer. I will be testing the indicated and
>> then I give you return.
>> Best regards,
>>
>> Yessica Brinkmann
>>
>>
>>  Mensaje original 
>> Asunto: Re: I think that my data is saved correctly, but when printing
>> again, other data appears
>> De: Tom Lane
>> Para: Yessica Brinkmann
>> CC: pgsql-general@lists.postgresql.org
>>
>>
>> Yessica Brinkmann writes:
>> > I have a function called get_columnnames, which runs on a Postgresql
>> server
>> > and returns the names of the columns of a table in a Postgresql
>> database.
>> > In the first foreach, in the statement: idxcd-> varattnames [cont] =
>> data;
>> > column names are saved. Those column names are saved correctly. I could
>> > verify this by printing some values.
>> > But later, in the following foreach, when I reprint the values of the
>> names
>> > of the columns that had been saved in the idxcd-> varattnames [cont] =
>> data
>> > statement; They appear to me with errors.
>>
>> Yeah, because the "data" value is just a pointer into the tupdesc
>> associated with the SPI result, and that disappears the moment you
>> do SPI_finish(). You'd need to do something to copy the strings
>> into a longer-lived context. A plain pstrdup() won't suffice
>> because you're in a short-lived SPI context already inside that
>> loop; but you could save CurrentMemoryContext before starting up
>> SPI and then use MemoryContextStrdup.
>>
>> regards, tom lane
>>
>>

Re: Gurjeet Singh Index Adviser User Interface

[Jony Cohen]

Hi Yessica,
Postgres 8.3 is very old, If you are using that simply because of the index 
advisor there are a few more “modern” options.

I at the time wrote such a tool for Postgres 9.4 (it has many features that the 
one by Gurjeet didn’t have like CTE and partial indexes support)
Alas I didn’t have a chance to update it to 9.6 & 10 as there were many 
breaking changes.
https://github.com/cohenjo/pg_idx_advisor 


And there is even a newer module to generate hypothetical indexes to allow you 
to test various options before building the actual index: 
https://github.com/HypoPG/hypopg  

You are missing so much functionality and features that any improvements you 
get by a better index recommendation will still be slower then running a new 
version with less optimal indexes.

I would consider using a new version.

Kind Regards,
Jony

> On 10 Oct 2020, at 3:19, Yessica Brinkmann  
> wrote:
> 
> I will greatly appreciate a help please with this topic. I really need a lot 
> to be able to use this interface to be able to test my thesis well. I really 
> don't quite understand what installing postgresql-server-dev-X.Y refers to. 
> And I really don't really know how to install it too. If you can give me a 
> guide at least about this please? And in which version should I install it? I 
> am using Postgresql 8.3.23, I really use this version because the Index 
> Adviser only works with this version of Postgresql. And also I do not 
> understand well if installing that already solves everything or should I 
> install an additional package?
> Best regards,
> Yessica Brinkmann.
> 
> 
>  
> 
>  Libre de virus. www.avg.com 
> 
>  
> El jue., 8 oct. 2020 a las 22:21, Yessica Brinkmann 
> (mailto:brinkmann.yess...@gmail.com>>) escribió:
> I also clarify that I tried to install the libpq-dev package already but 
> could not install it because it depends on a non-installable package called 
> libss10.9.8. And I had to apply apt --fix-broken install to fix the problem.
> 
>  
> 
>  Libre de virus. www.avg.com 
> 
>  
> El jue., 8 oct. 2020 a las 22:08, Yessica Brinkmann 
> (mailto:brinkmann.yess...@gmail.com>>) escribió:
> Goodnight,
> I apologize please that today I was able to test the directory, since I had a 
> health problem in recent days.
> Well, I actually found the files corresponding to the pg_advise_index 
> interface. But I was surprised that the executable is not present, but 
> apparently this interface must be compiled by another part of the index 
> adviser, to be able to execute it. It has a Makefile.
> I tried to compile as follows, and I get the following errors:
> root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise#
>  USE_PGXS = 1 make
> You need to install postgresql-server-dev-X.Y for building a server-side 
> extension or libpq-dev for building a client-side application.
> make: *** There are no targets. High.
> root@debian:/home/yessica/Descargas/postgresql-8.3.23/contrib/pg_adviser_master/pg_advise#
>  make install
> make: Nothing is done for 'install'.
> I will greatly appreciate a help please, regarding these errors that I got. 
> The truth is that I honestly  don't have much experience using Linux.
> Best regards,
> Yessica brinkmann
> 
>  
> 
>  Libre de virus. www.avg.com 
> 
>  
> 
> El mar., 29 sept. 2020 a las 18:55, Yessica Brinkmann 
> (mailto:brinkmann.yess...@gmail.com>>) escribió:
> Goodnight,
> Thank you very much for the answer.
> I followed all the installation instructions: apply patch, compile
> install postgres, etc.
> I just tried everything with the UI ii) Manually (through psql session), 
> which is also mentioned in the readme, and everything works for me. But to 
> better test my thesis, I would also need to use the other user interface 
> mentioned in the readme and that is precisely i) pg_advise_index tool.
> I understand what you are telling me about the directory. I will be testing 
> this way.
> Many thanks.
> Best regards,
> Yessica brinkmann
> 
> El mar., 29 sept. 2020 a las 18:21, Rob Sargent ( >) escribió:
> 
> 
> On 9/29/20 3:46 PM, Yessica Brinkmann wrote:
> > I will greatly appreciate a help with this topic please. I really need 
> > to use that in