how to check if the license is expired.
I want to develop a postgresql paid extension, then there is a local license file, how do I check if the license file is expired, check it once at each api execution, will that affect the performance of the api, is there any other way?
Re: create a temp table in SPI
Thanks for your explanation.
Laurenz Albe 于2023年7月13日周四 17:48写道：
> On Thu, 2023-07-13 at 13:12 +0800, 黄宁 wrote:
> > I want to create some temporary tables in SPI, but after I created the
> table and inserted the data, I can’t query any data, why?
> >
> > the postgres version:
> > PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0,
> 64-bit
> > code:
> >
> >
> > int ret = 0;
> > SPI_connect();
> > ret = SPI_execute("CREATE GLOBAL TEMP TABLE temp_table (id int,
> value text)", false, 0);
> >
> > ret = SPI_execute("INSERT INTO temp_table VALUES (1, 'a'), (2,
> 'b')", false, 0);
> >
> > ret = SPI_execute("SELECT * FROM temp_table", true, 0);
> >
> > if(SPI_processed > 0)
> ...
>
> That's because you set "read_only" to "true" when you executed the query,
> so that the command counter is not incremented, and the query cannot see
> the results from the previous statement.
>
> The documentation is quite clear here:
>
> It is generally unwise to mix read-only and read-write commands within
> a single function using SPI; that could result in very confusing
> behavior,
> since the read-only queries would not see the results of any database
> updates done by the read-write queries.
>
> Yours,
> Laurenz Albe
>
create a temp table in SPI
I want to create some temporary tables in SPI, but after I created the
table and inserted the data, I can’t query any data, why?
the postgres version:
PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
code:
int ret = 0;
SPI_connect();
ret = SPI_execute("CREATE GLOBAL TEMP TABLE temp_table (id int, value
text)", false, 0);
if(ret != SPI_OK_UTILITY)
{
elog(ERROR, "%s: create failed", __FUNCTION__);
}
ret = SPI_execute("INSERT INTO temp_table VALUES (1, 'a'), (2, 'b')",
false, 0);
if(ret != SPI_OK_INSERT)
{
elog(ERROR, "%s: insert failed", __FUNCTION__);
}
// SPI_commit();
ret = SPI_execute("SELECT * FROM temp_table", true, 0);
if(ret != SPI_OK_SELECT)
{
elog(ERROR, "%s: select failed", __FUNCTION__);
}
if(SPI_processed > 0)
{
}
else {
// elog(ERROR, "%s: find nothing", __FUNCTION__);
}
// SPITupleTable *tuptable = SPI_tuptable;
// TupleDesc tupdesc = tuptable->tupdesc;
// // the numvals is 0,but I insert two records
// if (tuptable->numvals > 0)
// {
// HeapTuple tuple = tuptable->vals[0];
// char *tupleval1 = SPI_getvalue(tuple, tupdesc, 1);
// char *tupleval2 = SPI_getvalue(tuple, tupdesc, 2);
// pfree(tupleval1);
// pfree(tupleval2);
// }
// else
// {
// elog(ERROR, "%s: can not query something", __FUNCTION__);
// }
// do something with tuptable
SPI_finish();
Re: cursor with hold must be save to disk?
the Postgresql version is 13.6 and the DECLARE COMMAND IS declare sdx_3a6c_8 no scroll binary cursor without hold for select "roalkL"."smid","roalkL"."smgeometry" from "public"."roalkL" where "roalkL"."smgeometry" && st_makeenvelope(321673.3153346270555630,3375950.6560412631370127,367212.1915803211741149,3402758.1912380573339760,32649) the data might be 1GB,and we need get all in about 10 seconds. Adrian Klaver 于2023年4月14日周五 23:11写道： > On 4/14/23 04:04, 黄宁 wrote: > > i want to use cursor with hold ,but when I declare a curosr , it takes a > > long time to save the result set to disk. can i save the query state in > > memory? and fetch forward the next result. > > > > From the docs: > > https://www.postgresql.org/docs/current/sql-declare.html > > A cursor created with WITH HOLD is closed when an explicit CLOSE command > is issued on it, or the session ends. In the current implementation, the > rows represented by a held cursor are copied into a temporary file or > memory area so that they remain available for subsequent transactions. > > So I am going to guess the cursor query is holding a large amount of data. > > To get a more specific answer you will need to provide: > > 1) Postgres version. > > 2) The complete DECLARE command being used. > > 3) An indication of the amount of data being retrieved. > > 4) The actual time for a 'long time'. > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
cursor with hold must be save to disk?
i want to use cursor with hold ,but when I declare a curosr , it takes a long time to save the result set to disk. can i save the query state in memory? and fetch forward the next result.
get table oid in GIN extracequery function
I want to query some custom statistics in extracequery of GIN index, how should I go to find which table this index belongs to。
change analyze function for a array type
I create a new type and want to change its array type analyze function. I use ALTER TYPE typename SET (ANALYZE = func); not worked?
write an analyze_function for own type
I now have some custom data like: [0x1 0x22 0x365] It has a level attribute, that is, the level of 0x1 is 1, and the level of 0x22 is 2. How should I count the minimum level in a table? I want to use this statistic in GIN index.
Delete a table automatic?
I now have two tables named A and B. Table B is calculated based on the data of table A. I wonder if table B can be automatically deleted when table A is deleted？
GIN Index Partial Match?
I create a gin index for a custom type. I want to use partial match, but I find extract query method called twice in a query, when in the first called, I return the minimal value of the type, and I want to set the maximal value of the type in extra data will be used in compare partial function ,But in the second extrace query function called,the query value is the first my return.It's weird. ,how i can set the extra data correctly? Thank You!
GIN Index use statistic information?
Hi: I create a new type for myself and create a gin index for its array type. I want to know : 1. how to use statistic info in build index for a table? 2. how to use analyze to generate statistic information?
