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
>
>

Re: Suppress logging of "pg_hba.conf rejects connection for host"

[Ron]

On 4/16/23 10:02, ertan.kucuko...@1nar.com.tr wrote:

Hello,

One of the systems running PostgreSQL 14.7 receive a lot of lines like in
the subject. I have below pg_hba.conf line and that line causes these to be
logged.

host all all 0.0.0.0/0 reject

If possible, I do not want to see these lines in my logs. But, I failed to
find a parameter for it.

Is it possible to turn this specific message logging off?


1. Why do you need that line, instead of just allowing in the addresses you 
want?

2. Why are you getting so many "illegal" connection attempts?
3. Auditors like to see that you're rejecting "illegal" connection attempts.

--
Born in Arizona, moved to Babylonia.

Question on SSI - Serializable Snapshot Isolation

[Siddharth Jain]

Hi All,

When using SSI , lets say we have two
transactions T1 and T2 and there is a serialization conflict. Postgres
knows when one or the other transaction is doomed to fail
[image: image.png]
but will not raise serialization error until the transaction commits. This
can cause a massive perf hit because the transactions could be long
running. Why not raise the error early on when the conflict is detected to
avoid wasting CPU and other resources? Can anyone explain this to me? Is
this an area that could be improved upon? Thanks.

S.

Re: Should I add a new schema for my application?

[Laurenz Albe]

On Sat, 2023-04-15 at 12:06 +0200, Alexander Farber wrote:
> I am seeking for a general advice if it would be a good idea for a 
> PostgreSQL/PostGIS using application to add a third schema.
> 
> I am using postgis/postgis Docker image which comes with "public" schema.
> 
> Then I have loaded OpenStreetMap data into the database and have created 
> "osm_schema" for that:
> 
> And now I need one more table, for the new mapping feature in my application, 
> which would reference the osm_id column in the planet_osm_roads.
> 
> I have not used database schemas at all until yet, I am looking for an advice 
> if I should add a third schema here, just for my application?

I would add another schema.

The "public" schema for the extensions, "osm_schema" for the OpenStreetMap data,
and a third schema for your table.  Perhaps you want to add additional objects 
later.

Yours,
Laurenz Albe

Re: Guidance on INSERT RETURNING order

[John Howroyd]

May I clarify the ideas being discussed so far, perhaps with a view to make
a relevant proposal.  My apologies if I get anything wrong or go too far.

As I understand it the proposal is to supplement the syntax to something
like:

INSERT INTO table (a, b, c)
VALUES ((1,2,3), (4,5,6), ...)
WITH ORDINALITY
RETURNING table.id, ordinality
;

The meaning of which is to adjoin an ordinality column to the output
reflecting the declaration order in the values clause.  So an output of
(not necessarily in any order):
(1001, 1)
(1003, 2)
means that table.id = 1001 was assigned to the inserted row from tuple
(1,2,3) (from VALUES, because that table.id is associated to ordinality =
1) and table.id = 1003 was assigned to the inserted row  from tuple
(4,5,6).  The output being ordered as determined by the internals of query
execution (not necessarily the one shown).

Is that correct?

I presume (although, not quite so clear) that one would have:

INSERT INTO table (a, b, c)
SELECT a_val, b_val, c_val
FROM joined_tables
WHERE some_condition
ORDER BY something_relevant
WITH ORDINALITY
RETURNING table.id, ordinality
;

The meaning being very much as before replacing 'declaration order' by 'row
order of the SELECT statement as defined by the ORDER BY clause'; so pretty
much like a row_number() but in the output of the RETURNING clause (and
without an OVER modification).  I added the ORDER BY clause as I don't
really see what this would mean without it; but this (presumably) does not
affect output order only the order of the incoming rows (and hence the
generation of the ordinality output).

Is that correct?

Might there be a natural syntax to label the 'ordinality' output column?
Perhaps something like:

...
WITH ORDINALITY (col_name)
RETURNING table.id, col_name
;

I don't want to clash with the syntax for Table Functions.

Is it a step too far to propose allowing an additional ORDER BY clause
after the RETURNING clause (a specific declaration for the query execution
to assign cpu cycles; especially if the WITH ORDINALITY is not tied to
output order)?

Personally, I didn't see Frederico's comment as anything to do with order;
just how one could output additional values in the RETURNING clause
(namely, v.num from a subexpression of the SELECT but in whatever order it
comes).  On the other hand, that seems a lot more complicated to me because
it is not an expression in the overall SELECT feeding the INSERT, whereas
the WITH ORDINALITY is a specific declaration to match input order with
output order by inserting a counter.

Apologies, if I have misunderstood or invented something that's not
possible!

Re: Guidance on INSERT RETURNING order

[John Howroyd]

PS:  Sorry, I haven't yet thought how this might work with UPDATE or MERGE,
but if I am on the right track with INSERT I'll give this some thought.

On Mon, 17 Apr 2023 at 18:48, John Howroyd  wrote:

> May I clarify the ideas being discussed so far, perhaps with a view to
> make a relevant proposal.  My apologies if I get anything wrong or go too
> far.
>
> As I understand it the proposal is to supplement the syntax to something
> like:
>
> INSERT INTO table (a, b, c)
> VALUES ((1,2,3), (4,5,6), ...)
> WITH ORDINALITY
> RETURNING table.id, ordinality
> ;
>
> The meaning of which is to adjoin an ordinality column to the output
> reflecting the declaration order in the values clause.  So an output of
> (not necessarily in any order):
> (1001, 1)
> (1003, 2)
> means that table.id = 1001 was assigned to the inserted row from tuple
> (1,2,3) (from VALUES, because that table.id is associated to ordinality =
> 1) and table.id = 1003 was assigned to the inserted row  from tuple
> (4,5,6).  The output being ordered as determined by the internals of query
> execution (not necessarily the one shown).
>
> Is that correct?
>
> I presume (although, not quite so clear) that one would have:
>
> INSERT INTO table (a, b, c)
> SELECT a_val, b_val, c_val
> FROM joined_tables
> WHERE some_condition
> ORDER BY something_relevant
> WITH ORDINALITY
> RETURNING table.id, ordinality
> ;
>
> The meaning being very much as before replacing 'declaration order' by
> 'row order of the SELECT statement as defined by the ORDER BY clause'; so
> pretty much like a row_number() but in the output of the RETURNING clause
> (and without an OVER modification).  I added the ORDER BY clause as I don't
> really see what this would mean without it; but this (presumably) does not
> affect output order only the order of the incoming rows (and hence the
> generation of the ordinality output).
>
> Is that correct?
>
> Might there be a natural syntax to label the 'ordinality' output column?
> Perhaps something like:
>
> ...
> WITH ORDINALITY (col_name)
> RETURNING table.id, col_name
> ;
>
> I don't want to clash with the syntax for Table Functions.
>
> Is it a step too far to propose allowing an additional ORDER BY clause
> after the RETURNING clause (a specific declaration for the query execution
> to assign cpu cycles; especially if the WITH ORDINALITY is not tied to
> output order)?
>
> Personally, I didn't see Frederico's comment as anything to do with order;
> just how one could output additional values in the RETURNING clause
> (namely, v.num from a subexpression of the SELECT but in whatever order it
> comes).  On the other hand, that seems a lot more complicated to me because
> it is not an expression in the overall SELECT feeding the INSERT, whereas
> the WITH ORDINALITY is a specific declaration to match input order with
> output order by inserting a counter.
>
> Apologies, if I have misunderstood or invented something that's not
> possible!
>

Re: Question on SSI - Serializable Snapshot Isolation

[Tom Lane]

Siddharth Jain  writes:
> When using SSI , lets say we have two
> transactions T1 and T2 and there is a serialization conflict. Postgres
> knows when one or the other transaction is doomed to fail
> [image: image.png]

Please don't use images for things that could perfectly well be
expressed as text.  They're not quotable, they may not show up
in the archives (as this one doesn't), etc etc.  email is a text
medium, despite Google's attempts to convince you otherwise.

> but will not raise serialization error until the transaction commits. This
> can cause a massive perf hit because the transactions could be long
> running. Why not raise the error early on when the conflict is detected to
> avoid wasting CPU and other resources? Can anyone explain this to me?

Try src/backend/storage/lmgr/README-SSI, notably this bit:

* This SSI implementation makes an effort to choose the
transaction to be canceled such that an immediate retry of the
transaction will not fail due to conflicts with exactly the same
transactions.  Pursuant to this goal, no transaction is canceled
until one of the other transactions in the set of conflicts which
could generate an anomaly has successfully committed.  This is
conceptually similar to how write conflicts are handled.

The main point here is that "at least one of these transactions
will have to fail" is very different from "all of these transactions
have to fail".  If the implementation prematurely forecloses on one
of them, it may be that *no* useful work gets done because the others
also fail later on for other reasons; moreover, it might be that the
victim transaction could have committed after those others failed.
Withholding judgment about which one to cancel until something has
committed ensures that more than zero work gets completed.

Also note that AFAICS we do notice fairly promptly once a transaction
has been marked as doomed; it's not the case that we wait till the
transaction's own commit to check that.

regards, tom lane

Re: Guidance on INSERT RETURNING order

[Federico]

On Tue, 18 Apr 2023 at 00:21, John Howroyd  wrote:
>
> May I clarify the ideas being discussed so far, perhaps with a view to make a 
> relevant proposal.  My apologies if I get anything wrong or go too far.
>
> As I understand it the proposal is to supplement the syntax to something like:
>
> INSERT INTO table (a, b, c)
> VALUES ((1,2,3), (4,5,6), ...)
> WITH ORDINALITY
> RETURNING table.id, ordinality
> ;
>
> The meaning of which is to adjoin an ordinality column to the output 
> reflecting the declaration order in the values clause.  So an output of (not 
> necessarily in any order):
> (1001, 1)
> (1003, 2)
> means that table.id = 1001 was assigned to the inserted row from tuple 
> (1,2,3) (from VALUES, because that table.id is associated to ordinality = 1) 
> and table.id = 1003 was assigned to the inserted row  from tuple (4,5,6).  
> The output being ordered as determined by the internals of query execution 
> (not necessarily the one shown).
>
> Is that correct?

That would work as syntax for the task of tracking what id or other
server default is generated by a value clause tuple.

> I presume (although, not quite so clear) that one would have:
>
> INSERT INTO table (a, b, c)
> SELECT a_val, b_val, c_val
> FROM joined_tables
> WHERE some_condition
> ORDER BY something_relevant
> WITH ORDINALITY
> RETURNING table.id, ordinality
> ;
>
> The meaning being very much as before replacing 'declaration order' by 'row 
> order of the SELECT statement as defined by the ORDER BY clause'; so pretty 
> much like a row_number() but in the output of the RETURNING clause (and 
> without an OVER modification).  I added the ORDER BY clause as I don't really 
> see what this would mean without it; but this (presumably) does not affect 
> output order only the order of the incoming rows (and hence the generation of 
> the ordinality output).
>
> Is that correct?

This would not be needed if the syntax with VALUES WITH ORDINALITY is
added in sqlalchemy. So fine either way.
If "WITH ORDINALITY" is a feature of VALUES this syntax would not be
allowed though. I'm personally ok limiting WITH ORDINALITY only to
VALUES.

> Might there be a natural syntax to label the 'ordinality' output column?  
> Perhaps something like:
>
> ...
> WITH ORDINALITY (col_name)
> RETURNING table.id, col_name
> ;
>
> I don't want to clash with the syntax for Table Functions.
>
> Is it a step too far to propose allowing an additional ORDER BY clause after 
> the RETURNING clause (a specific declaration for the query execution to 
> assign cpu cycles; especially if the WITH ORDINALITY is not tied to output 
> order)?
>
> Personally, I didn't see Frederico's comment as anything to do with order; 
> just how one could output additional values in the RETURNING clause (namely, 
> v.num from a subexpression of the SELECT but in whatever order it comes).  On 
> the other hand, that seems a lot more complicated to me because it is not an 
> expression in the overall SELECT feeding the INSERT, whereas the WITH 
> ORDINALITY is a specific declaration to match input order with output order 
> by inserting a counter.

I didn't mean to suggest any particular order should be kept by insert
or by returning. I was merely commenting on the David G. Johnston
reply

 I suppose breaking the restriction that only columns present on
the insertion-table can be returned is a possible option that also
solves another infrequent request.

> Apologies, if I have misunderstood or invented something that's not possible!

Thanks for the recap. I'm hoping this can become a proposal.

Best,
  Federico

Fwd: Request for information about Postgres version 15.2 stability

[gayathri ramesh]

-- Forwarded message -
From: gayathri ramesh <17gayathriram...@gmail.com>
Date: Tue, 18 Apr, 2023, 12:20 pm
Subject: Request for information about Postgres version 15.2 stability
To: 


Hi Team,

Our company is currently supporting around 100+ postgres databases and we
are planning to upgrade to version 15.2. We want to ensure that there are
no major critical bugs that could impact our production  systems.

I would appreciate if you could provide us with information about the
stability of postgres version 15.2. We are particularly interested in
knowing if there are any known critical bugs or issues that could
potentially impact the system.

Thanks in advance. looking forward to hearing from you.

Best Regards.

Request for information about postgres version 15.2 stability

[gayathri ramesh]

Hi Team,

Our company is currently supporting around 100+ postgres databases and we
are planning to upgrade to version 15.2. We want to ensure that there are
no major critical bugs that could impact our production systems.

I would appreciate if you could provide us with information about the
stability of postgres version 15.2. We are particularly interested in
knowing if there are any known critical bugs or issues that could
potentially impact the system.

Thanks in advance. looking forward to hearing from you.

Best Regards.