listening on an address other than 'localhost'?

[Johnson, Bruce E - (bjohnson)]

per the documentation to listen for client connections on a given IP address 
you set listen_addresses to a commaspearated list of IP addresses in 
postgresql.conf

That line is commented out in the default conf file, when I uncomment it, and 
add the address, then do sudo systemctl restart postgresql the postgres 
processes are not running afterwards.

root@dhbpostgres:/home/johnson# ps -aux | grep postgres
root1452  0.0  0.0   6608  2364 pts/1S+   19:07   0:00 grep 
--color=auto postgres
root@dhbpostgres:/home/johnson# vi /etc/postgresql/15/main/postgresql.conf 
 apt repo.


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Helmut Bender]

Am 06.09.23 um 16:04 schrieb Sai Teja:
In my local it is windows OS and locale is English_united_states.1252 
and in local it is converting as expected ( testµ into TESTµ)


You wrote PostgreSQL is hosted on an utf-8 system. That's why it's 
working different.


And be careful: the M isn't the uppercase latin m, but in fact the 
uppercase µ (U+039C in unicode)! If you would compare it to latin 
alphabet 'TESTM' it would be different.


--
Gruß Helmut

Re: listening on an address other than 'localhost'?

[Daniel Gustafsson]

> On 6 Sep 2023, at 21:23, Johnson, Bruce E - (bjohnson) 
>  wrote:

> What am I missing? (postgresql 15.4 running on Ubuntu 22.04), installed via 
> postgresql.org apt repo.

The value should be within '', so listen_addresses = '10.128.206.109' should
work just fine.

--
Daniel Gustafsson

Re: listening on an address other than 'localhost'?

[Erik Wienhold]

On 06/09/2023 21:23 CEST Johnson, Bruce E - (bjohnson) 
 wrote:

> Sep 6 19:06:01 dhbpostgres postgresql@15-main[1538]: 2023-09-06 19:06:01.181 
> GMT [1543] LOG: syntax error in file 
> "/etc/postgresql/15/main/postgresql.conf" line 63, near token ".206"
>
> #listen_addresses = 10.128.206.109# what IP address(es) to listen on;
> # comma-separated list of addresses;
> # defaults to 'localhost'; use '*' for all
> # (change requires restart)

Put the entire list of addresses in quotes, e.g. 'localhost,10.128.206.109'.

--
Erik

ERROR: stack depth limit exceeded

[gzh]

Hi, 




I upgraded the version of PostgreSQL from 12.13 to 12.16. 

If multiple subqueries or a large number of UNION ALL were used, 

the previously unseen error message "ERROR: stack depth limit exceeded" will 
appear after upgrading the version.




I understand that increasing the value of max_stack_depth can prevent code 
errors, 

but is there a good way to find out which SQL statements have issues without 
having to run all SQL statements, as it would be too expensive?




Regards

Re: Query performance going from Oracle to Postgres

[David Rowley]

On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan  wrote:
> It seems likely that the problem here is that some of the predicates
> appear as so-called "Filter:" conditions, as opposed to true index
> quals.

hmm, if that were true we'd see "Rows Removed by Filter" in the
explain analyze.

I think all that's going on is that each tuple is on a different page
and the heap accesses are just causing many buffers to be accessed. It
seems to me that an IOS would likely fix that problem which is why I
suggested count(*) instead of
count()

David

Re: postgreSQL UPPER Method is converting the character "µ" into "M"

[Joe Conway]

On 9/6/23 11:22, Helmut Bender wrote:

Am 06.09.23 um 16:04 schrieb Sai Teja:
In my local it is windows OS and locale is English_united_states.1252 
and in local it is converting as expected ( testµ into TESTµ)


You wrote PostgreSQL is hosted on an utf-8 system. That's why it's
working different.

And be careful: the M isn't the uppercase latin m, but in fact the
uppercase µ (U+039C in unicode)! If you would compare it to latin
alphabet 'TESTM' it would be different.



Yep, there are interactions between the encoding used by your terminal, 
the server encoding, and the client encoding.


You have to be particularly careful when cutting and pasting that the 
client encoding and your terminal encoding match or you can get 
surprising results.


--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Re: ERROR: stack depth limit exceeded

[Tom Lane]

gzh  writes:
> I upgraded the version of PostgreSQL from 12.13 to 12.16. 
> If multiple subqueries or a large number of UNION ALL were used, 
> the previously unseen error message "ERROR: stack depth limit exceeded" will 
> appear after upgrading the version.

Please provide a self-contained example.  The people who might be
able to fix this are too busy to guess about what you are seeing.

regards, tom lane

Re: ERROR: stack depth limit exceeded

[Geoff Winkless]

On Thu, 7 Sept 2023 at 08:45, gzh  wrote:
> but is there a good way to find out which SQL statements have issues without 
> having to run all SQL statements, as it would be too expensive?

Does your postgresql server log not contain the error with the
statement at issue?

Geoff

Feedback on supporting AI queries in Postgres with EvaDB

[Arulraj, Joy]

Greetings!


We are developing the EvaDB query engine 
[https://github.com/georgia-tech-db/evadb] for supporting AI queries in 
Postgres. Our goal is to assist software developers in building AI-powered apps 
with SQL. EvaDB allows querying data with AI models in this way:


— Convert the super bowl audio to text using a speech-to-text model

CREATE TABLE transcript AS

SELECT SpeechToText(audio) FROM super_bowl_video;


— Run a ChatGPT-based query on the derived text column

SELECT ChatGPT('When did touchdowns happen in this game", text)

FROM transcript;


Here is a more interesting query for analyzing sentiments expressed in food 
reviews and generating responses for addressing reviews with negative sentiment:


— Analyze sentiments in reviews and respond to “negative” reviews using ChatGPT

SELECT ChatGPT(

 "Respond to the review with a solution to address the 
reviewer's concern",

  review)

FROM postgres_data.review_table

WHERE ChatGPT(

"Is the review positive or negative? Only reply 'positive' 
or 'negative'.",

review) = "negative"

  AND location = “waffle house”;


To process this query, EvaDB’s optimizer pushes the scan operator at the bottom 
of the query plan down to Postgres (SELECT review FROM 
postgres_data.review_table;) and takes care of executing the other operators 
(function expression evaluation, filtering, etc.) 
[https://evadb.readthedocs.io/en/stable/source/usecases/sentiment-analysis.html].
 The optimizer is focused on accelerating queries with AI functions written in 
Python. For example, it automatically reorders query predicates based on the 
cost of evaluating them. While processing the review analysis query, EvaDB 
first filters out tuples from restaurants other than “waffle house”, and then 
processes the remaining tuples with ChatGPT to speed up the query.


As such optimizations are done outside Postgres, I am referring to them as 
“external” query optimization. Besides predicate reordering, EvaDB supports 
parallel query execution, function caching, etc. for speeding up AI queries 
[https://github.com/georgia-tech-db/evadb/tree/staging/evadb/optimizer].


We would appreciate feedback on:

  1.  How can we better use Postgres to accelerate such AI queries? Is 
PL/Python relevant for this use-case?

  2.  What are some prior “external” query optimization systems tailored for 
Postgres that we can learn from?



Thanks for your time,

Joy

---
Joy Arulraj
Assistant Professor, School of Computer Science
Georgia Tech

Re: listening on an address other than 'localhost'?

[Adrian Klaver]

On 9/6/23 12:23, Johnson, Bruce E - (bjohnson) wrote:
per the documentation to listen for client connections on a given IP 
address you set listen_addresses to a commaspearated list of IP 
addresses in postgresql.conf





#listen_addresses = 10.128.206.109# what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)


What am I missing? (postgresql 15.4 running on Ubuntu 22.04), installed 
via postgresql.org  apt repo.



The other replies have indicated how you should format the value. For 
future reference the postgresql.conf configuration docs:


https://www.postgresql.org/docs/current/runtime-config.html

tell you what is expected. In this case:

https://www.postgresql.org/docs/current/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS

listen_addresses (string)

vs

port (integer)





--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Will PostgreSQL 16 supports native transparent data encryption ?

[Stephen Frost]

Greetings,

* Ron (ronljohnso...@gmail.com) wrote:
> On 8/24/23 14:08, Stephen Frost wrote:
> > * Ron (ronljohnso...@gmail.com) wrote:
> > > On 8/21/23 18:49, Bruce Momjian wrote:
> > > > On Mon, Aug 21, 2023 at 07:02:46PM +0300, Mostafa Fathy wrote:
> > > > > It is mentioned here 
> > > > > https://www.postgresql.org/about/press/faq/#:~:text=
> > > > > Q%3A%20What%20features%20will%20PostgreSQL%2016%20have%3F that native
> > > > > transparent data encryption is being worked on and it may be 
> > > > > delivered with
> > > > > PostgreSQL 16.
> > > > > 
> > > > > Is PostgreSQL 16 beta version includes native transparent data 
> > > > > encryption or
> > > > > not ? because I checked the docs 
> > > > > https://www.postgresql.org/docs/16/index.html
> > > > > and couldn't find anything related to transparent data encryption.
> > > > > 
> > > > > If not supported yet in the beta version I would like to know if 
> > > > > PostgreSQL 16
> > > > > final version will support native transparent data encryption or not?
> > > > Not, PG 16 will not support it, and I am unclear if later major versions
> > > > will either.
> > > That's disappointing, since TDE makes PCI audits that much simpler.
> > There's ongoing work happening for TDE support and we'd love to hear
> > from folks who would like to see it included.
> 
> PgBackRest currently encrypts it's binary backups.

pgbackrest is optionally able to encrypt backups, sure, and that's
certainly a good thing, though having a way for the process performing
the backup to not be able to actually see the unencrypted data in the
first place eliminates that as an attack vector.

> 1. What kind of encryption would there be?  AES256 makes the auditors happy.

Supported options for AES would be 128, 192 and 256.

> 2. Would TDE-enabled pg_dump create encrypted dump files?

No, pg_dump is a client utility and hasn't got anything to do with TDE
really.

> 3. Would TDE obviate the need for PgBackRest's encryption?

The short answer to this is 'probably yes, when TDE is enabled on the
cluster'.  Clearly, pgbackrest would continue to support encryption and
there will be some things in PG that aren't encrypted that it might be
nice to have encrypted, depending on your particular security folks, but
you wouldn't need pgbackrest's encryption to ensure that the principle
user data is encrypted.

> 4. How would encrypted "pg_dump --format=plain" work?  Or could it only work
> with the other formats (which is fine by me)?

pg_dump isn't impacted by TDE.

> >You can expect an updated patch set for the September commitfest.
> 
> For that which will be Pg 17?

Probably still optimistic to be thinking about this for PG17, but
hopefully some of the preliminary work will be able to get into PG17
even if full TDE does not.

> > Getting more folks to test it
> > and use it and review it would certainly help move it forward.
> 
> By any chance, will binaries be created after the September commitfest? 
> (Hoops must be jumped through to get development packages installed on the
> database servers I have access to, but I'd jump through them if needed.)

This would be something to ask the package maintainers... but I tend to
doubt they'd want the additional work; there's already lots and lots of
packages they're dealing with and to add on packages for every patch
that's posted to the PG mailing lists would be a huge task..

Thanks,

Stephen


signature.asc
Description: PGP signature

Only getting few records inserted from millions

[Anthony Apollis]

Please assist.

I am retrieving data from SAP BW using SSIS. The data is loaded into:

CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
(
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"RowInsertedTimestamp" date DEFAULT CURRENT_DATE,
"RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
"Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass),
"Source_Description_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass),
"Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass),
"Entity_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass),
"Region_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass),
CONSTRAINT "ZTBR_TA_BW_PrimaryKey" PRIMARY KEY
("Master_BRACS_Secondary_Key")
)

TABLESPACE pg_default;

Destination table is:

CREATE TABLE IF NOT EXISTS model."IMETA_ZTBR_BRACS_Model_TA_BW"
(
"Company_Code" character varying(4) COLLATE pg_catalog."default",
"Posting_Period" character varying(7) COLLATE pg_catalog."default",
"Fiscal_Year" character varying(4) COLLATE pg_catalog."default",
"Profit_Center" character varying(255) COLLATE pg_catalog."default",
"Account_Number" character varying(255) COLLATE pg_catalog."default",
"Business_Process" character varying(255) COLLATE pg_catalog."default",
"Cost_Center" character varying(10) COLLATE pg_catalog."default",
"Internal_Order" character varying(255) COLLATE pg_catalog."default",
"Trading_Partner" character varying(255) COLLATE pg_catalog."default",
"Amount_in_company_code_currency" numeric(17,2),
"Company_code_currency" character varying(5) COLLATE pg_catalog."default",
"BRACS_FA" character varying(255) COLLATE pg_catalog."default",
"Expense_Type" character varying(255) COLLATE pg_catalog."default",
"BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
"CC_Direct" character varying(255) COLLATE pg_catalog."default",
"Segment_PC" character varying(255) COLLATE pg_catalog."default",
"CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
"Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass),
"Source_Description_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass),
"Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass),
"Entity_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass),
"Region_Secondary_Key" integer NOT NULL DEFAULT
nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass),
"RowInsertedTimestamp" date DEFAULT CURRENT_DATE,
"RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "ZTBR_Query_Destination_pkey" PRIMARY KEY
("Master_BRACS_Secondary_Key")
)


I get the data into the destination by joining a few tables:

ALTER TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"
OWNER to
---

INSERT INTO model."IMETA_ZTBR_BRACS_Model_TA_BW" (
"Company_Code",
"Posting_Period",
"Fiscal_Year",
"Profit_Center",
"Account_Number",
"Business_Process",
"Cost_Center",
"Internal_Order",

Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

[Sai Teja]

Hi All,

We have one table which is storing XML data with 30k records and stores
huge amount of data.

We are trying to create the index for this column in the table. But, we’re
getting “Huge input Lookup error” during creation of Index.

Please check the below command which is used to create the index

CREATE INDEX xml_index on xml_table using BTREE (CAST (XPATH
(‘directory/access/mode/@Identifier’, content) as text[]))

Here we’re using Xpath expression to create the index since postgreSQL
directly does not support comparison methods. So, we decided to use Xpath
expression. But while creating the index as I mentioned above we’re facing
the issue with Huge Input lookup

I can able to create the index when the table have no records. But now we
have huge amount of data stored in the table. So, creating the index facing
the issue with Huge input lookup

The same error we have faced earlier when trying to retrieve the particular
rows from the table So we have changed the XML option from content to
document and then it got worked and we can able to retrieve the files now.

But, now while creating the index, we tried to change the XML Option from
content to document again. But this didn’t worked.

PS, we are using postgreSQL 14.8 version which is hosted in azure.

I would request to please suggest any ideas to resolve this issue. This
would help us a lot and appreciated

Re: Only getting few records inserted from millions

[Erik Wienhold]

On 07/09/2023 19:14 CEST Anthony Apollis  wrote:

> The problem is that I am only getting 19 rows inserted into my destination
> table. My source has over 5 million rows. What am I doing wrong?

Few ideas:

* Column dim."IMETA_Entity_Mapping"."Entity_Secondary_Key" allows NULL but the
  join condition in your SELECT does not account for that.  That may be the
  reason you get less rows than expected.  The columns of the other join
  conditions are all NOT NULL as far as I can tell.

* Index corruption is also one possibility.

* Are there BEFORE triggers on the destination table that may prevent the INSERT
  of most rows (assuming that the SELECT returns the expected rows)?

--
Erik

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

[Tom Lane]

Sai Teja  writes:
> We have one table which is storing XML data with 30k records and stores
> huge amount of data.
> We are trying to create the index for this column in the table. But, we’re
> getting “Huge input Lookup error” during creation of Index.

There is no such string anywhere in the Postgres source code;
furthermore, if someone tried to add such an error, it'd get rejected
(I hope) as not conforming to our style guidelines.  I thought maybe
it's coming from libxml or the xpath code, but I couldn't get a match
for it anywhere in Debian Code Search either.  Is that the *exact*
spelling of the message?  Are you sure it's not coming from your
client-side code?

regards, tom lane

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

[Erik Wienhold]

On 07/09/2023 21:09 CEST Tom Lane  wrote:

> Sai Teja  writes:
> > We have one table which is storing XML data with 30k records and stores
> > huge amount of data.
> > We are trying to create the index for this column in the table. But, we’re
> > getting “Huge input Lookup error” during creation of Index.
> 
> There is no such string anywhere in the Postgres source code;
> furthermore, if someone tried to add such an error, it'd get rejected
> (I hope) as not conforming to our style guidelines.  I thought maybe
> it's coming from libxml or the xpath code, but I couldn't get a match
> for it anywhere in Debian Code Search either.  Is that the *exact*
> spelling of the message?  Are you sure it's not coming from your
> client-side code?

Looks like "Huge input lookup" as reported in [1] (also from Sai) and that
error is from libxml.

[1] 
https://www.postgresql.org/message-id/CADBXDMV_D3pBaSA5ZfUWnQP42ZO1YsbP9GZv8t1FncdGTiLMug%40mail.gmail.com

--
Erik

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

[David G. Johnston]

On Thu, Sep 7, 2023 at 12:28 PM Sai Teja 
wrote:

> Here we’re using Xpath expression to create the index since postgreSQL
> directly does not support comparison methods. So, we decided to use Xpath
> expression. But while creating the index as I mentioned above we’re facing
> the issue with Huge Input lookup
>

It doesn't support comparison methods of stuff like this because doing so
tends to be pointless or very expensive, setting aside the fact that, as
you probably are seeing here, records in an index must be small enough to
fit on a physical page and large bodies of text typically don't.

If you truly want to perform equality checks on large bodies of text the
typical solution is to hash said text and then perform a comparison against
hashes.

Since you are producing an array you might be able to get something like a
GIN or GIST index to work...I'm not all that familiar with them but they
were designed for non-atomic data values.

David J.

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

[Tom Lane]

Erik Wienhold  writes:
> On 07/09/2023 21:09 CEST Tom Lane  wrote:
>> There is no such string anywhere in the Postgres source code;
>> furthermore, if someone tried to add such an error, it'd get rejected
>> (I hope) as not conforming to our style guidelines.  I thought maybe
>> it's coming from libxml or the xpath code, but I couldn't get a match
>> for it anywhere in Debian Code Search either.  Is that the *exact*
>> spelling of the message?

> Looks like "Huge input lookup" as reported in [1] (also from Sai) and that
> error is from libxml.

Ah, thanks for the pointer.  It looks like for the DOCUMENT case,
we could maybe relax this restriction by passing the XML_PARSE_HUGE
option to xmlCtxtReadDoc().  However, there are things to worry about:

* Some of the other libxml functions we use don't seem to have an
options argument, so it's not clear how to remove the limit in all
code paths.

* One of the first hits I got while googling for XML_PARSE_HUGE was
CVE-2022-40303 [1] (libxml2: integer overflows with XML_PARSE_HUGE).
It seems highly likely that not everybody's libxml is patched for
that yet, meaning we'd be opening a lot of systems to security issues.

* XML_PARSE_HUGE apparently also removes restrictions on nesting
depth of XML documents.  I wonder whether that creates a risk of
stack-overflow crashes.

On the whole, I'm not sure I want to mess with this.  libxml2 is
rickety enough already without taking off its training wheels.
And, as noted by David J., we'd very possibly only be moving
the bottleneck somewhere else.  "Put many megabytes of data into
one field" is an antipattern for successful SQL use, and probably
always will be.

regards, tom lane

[1] https://bugzilla.redhat.com/show_bug.cgi?id=2136266

Re: Will PostgreSQL 16 supports native transparent data encryption ?

[Stephen Frost]

Greetings,

* Matthias Apitz (g...@unixarea.de) wrote:
> message from Stephen Frost mailto:sfr...@snowman.net>
> > * Ron (ronljohnso...@gmail.com ) wrote:
> > > On 8/21/23 18:49, Bruce Momjian wrote:
> > > > On Mon, Aug 21, 2023 at 07:02:46PM +0300, Mostafa Fathy wrote:
> > > > > It is mentioned here 
> > > > > https://www.postgresql.org/about/press/faq/#:~:text= 
> > > > > 
> > > > > Q%3A%20What%20features%20will%20PostgreSQL%2016%20have%3F that native
> > > > > transparent data encryption is being worked on and it may be 
> > > > > delivered with
> > > > > PostgreSQL 16.
> > > > > 
> > > > > Is PostgreSQL 16 beta version includes native transparent data 
> > > > > encryption or
> > > > > not ? because I checked the docs 
> > > > > https://www.postgresql.org/docs/16/index.html 
> > > > > 
> > > > > and couldn't find anything related to transparent data encryption.
> > > > > 
> > > > > If not supported yet in the beta version I would like to know if 
> > > > > PostgreSQL 16
> > > > > final version will support native transparent data encryption or not?
> > > > Not, PG 16 will not support it, and I am unclear if later major versions
> > > > will either.
> > > 
> > > That's disappointing, since TDE makes PCI audits that much simpler.
> > 
> > 
> > There's ongoing work happening for TDE support and we'd love to hear
> > from folks who would like to see it included. You can expect an updated
> > patch set for the September commitfest. Getting more folks to test it
> > and use it and review it would certainly help move it forward.
> 
> We have strong interest in TDE support and I would be happy to
> test this with our Library Management System.

Great, glad to hear that.  Note that this is still very much a
development effort and so some familiarity with how to build PostgreSQL
from source, apply patches, and then run the resulting binaries is
necessary to test.  If you're still interested, there's patches that
have been posted, just let me know.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Query performance going from Oracle to Postgres

[Peter Geoghegan]

On Thu, Sep 7, 2023 at 3:48 AM David Rowley  wrote:
> On Thu, 7 Sept 2023 at 19:17, Peter Geoghegan  wrote:
> > It seems likely that the problem here is that some of the predicates
> > appear as so-called "Filter:" conditions, as opposed to true index
> > quals.
>
> hmm, if that were true we'd see "Rows Removed by Filter" in the
> explain analyze.

That's why I hedged, with "seems likely". The problem with using
filter conditions rather than true index quals isn't limited to the
problem of extra heap accesses. It happened to be convenient to make
my point that way, but that isn't particularly fundamental here.

I deliberately chose to make my example involve an index-only scan
(that doesn't require any heap accesses) for this reason.

> I think all that's going on is that each tuple is on a different page
> and the heap accesses are just causing many buffers to be accessed.

This index is an absolute monstrosity. I find it far easier to believe
that the real explanation is the one that Steve intuited: that there
is an issue with the way that the physical data structures (which are
more or less comparable in both systems) are accessed in Postgres.

The index in question
("history_event_display_timesta_prism_guid_display_timestamp_idx1")
has certain columns that are omitted from the query. These columns
nevertheless appear between other columns that the query filters on.
The leading two columns ("prism_guid" and "display_timestamp") are
made into index quals by Postgres, but the next index column after
those two ("unique_lookup_key") is omitted by the query, and so isn't an index
qual. In fact *four* columns are omitted after that one. But, the very-low-order
index column "product_sid" *does* appear in the query, and so also
appears as Postgres index quals.

There is every chance that the true underlying explanation is that
Oracle is able to skip over significant parts of the index structure
dynamically. In fact I'm practically certain that that's the case,
since the "product_sid" column appears as an "access predicate", rather
than as a "filter predicate". These terms are explained here:

https://use-the-index-luke.com/sql/explain-plan/oracle/filter-predicates

https://use-the-index-luke.com/sql/explain-plan/postgresql/filter-predicates

How could "product_sid" be used as an "access predicate" given the
omitted index columns? It seems very likely that parts of the index
can be skipped in Oracle, but not in Postgres -- at least not yet.

Like Markus Winand, I think that it's a real problem that EXPLAIN
doesn't yet expose the difference between access predicates and filter
predicates. Some of the index quals shown by EXPLAIN are marked
SK_BT_REQFWD/SK_BT_REQBKWD by nbtree (meaning that they're what Oracle
calls "access predicates"), while other are not (meaning that they're
what Oracle calls "filter predicates"). That issue makes it far harder to spot
these sorts of problems.

> It seems to me that an IOS would likely fix that problem which is why I
> suggested count(*) instead of
> count()

Probably, but why should we have to use an index-only scan? And what
about my example, that already used one, and still showed a big
disparity where there is no inherently reason why there had to be? My
example didn't involve jumping to another part of the index because the
problem seems to be more general than that.

--
Peter Geoghegan

Re: Huge input lookup exception when trying to create the index for XML data type column in postgreSQL

[Sai Teja]

Thank you so much for all your responses.

I just tried with Hash, GIN etc

But it didn't worked. And I think it is because of "Xpath" expression which
I used in the index create command.

But is there any alternative way to change this Xpath? Since I need to
parse the XML as there is no other option. I need the other ways to create
the index .

May be if there are any parameters to change like xmloption etc it would
help us to resolve the issue.

Thanks,
Sai



On Fri, 8 Sep, 2023, 1:51 am Tom Lane,  wrote:

> Erik Wienhold  writes:
> > On 07/09/2023 21:09 CEST Tom Lane  wrote:
> >> There is no such string anywhere in the Postgres source code;
> >> furthermore, if someone tried to add such an error, it'd get rejected
> >> (I hope) as not conforming to our style guidelines.  I thought maybe
> >> it's coming from libxml or the xpath code, but I couldn't get a match
> >> for it anywhere in Debian Code Search either.  Is that the *exact*
> >> spelling of the message?
>
> > Looks like "Huge input lookup" as reported in [1] (also from Sai) and
> that
> > error is from libxml.
>
> Ah, thanks for the pointer.  It looks like for the DOCUMENT case,
> we could maybe relax this restriction by passing the XML_PARSE_HUGE
> option to xmlCtxtReadDoc().  However, there are things to worry about:
>
> * Some of the other libxml functions we use don't seem to have an
> options argument, so it's not clear how to remove the limit in all
> code paths.
>
> * One of the first hits I got while googling for XML_PARSE_HUGE was
> CVE-2022-40303 [1] (libxml2: integer overflows with XML_PARSE_HUGE).
> It seems highly likely that not everybody's libxml is patched for
> that yet, meaning we'd be opening a lot of systems to security issues.
>
> * XML_PARSE_HUGE apparently also removes restrictions on nesting
> depth of XML documents.  I wonder whether that creates a risk of
> stack-overflow crashes.
>
> On the whole, I'm not sure I want to mess with this.  libxml2 is
> rickety enough already without taking off its training wheels.
> And, as noted by David J., we'd very possibly only be moving
> the bottleneck somewhere else.  "Put many megabytes of data into
> one field" is an antipattern for successful SQL use, and probably
> always will be.
>
> regards, tom lane
>
> [1] https://bugzilla.redhat.com/show_bug.cgi?id=2136266
>

Re: ERROR: stack depth limit exceeded

[gzh]

I'm sorry I didn't explain the issue clearly. 

Our system is currently running on PostgreSQL 12.13 and we are planning to 
upgrade to PostgreSQL 12.16. 

We are currently in the evaluation phase. 

In the Release Notes for PostgreSQL 12.14, we saw the following change:

https://www.postgresql.org/docs/release/12.14/




>Add recursion and looping defenses in subquery pullup (Tom Lane)

>

>A contrived query can result in deep recursion and unreasonable amounts of 
>time spent trying to flatten subqueries. A proper fix for that seems unduly 
>invasive for a back-patch, but we can at least add stack depth checks and an 
>interrupt check to allow the query to be cancelled.




Our understanding is that this change will cause some complex SQL statements 

that were previously not reporting errors to report errors in the new version. 

If our understanding of this change is correct, we would like to find out 

which SQL statements will report errors in the new version. 

Do you have any good methods for doing this?











At 2023-09-07 21:29:56, "Tom Lane"  wrote:
>gzh  writes:
>> I upgraded the version of PostgreSQL from 12.13 to 12.16. 
>> If multiple subqueries or a large number of UNION ALL were used, 
>> the previously unseen error message "ERROR: stack depth limit exceeded" will 
>> appear after upgrading the version.
>
>Please provide a self-contained example.  The people who might be
>able to fix this are too busy to guess about what you are seeing.
>
>   regards, tom lane

pg_restore unexpected end of file

[Les]

I'm trying to migrate a database from version 11 to version 15.

I have created a dump file on v11:

pg_dump --dbname=not_telling -Fc --no-owner > dump

Then I was trying to import it on v15:

pg_restore -v --dbname=not_telling --no-user /restore/dump
pg_restore: error: could not read from input file: end of file

The file is 525MB. First I thought that maybe the dump is corrupt, so I
re-dumped the db again, but I got the same results. pg_dump does not report
any error, but pg_restore cannot import it.

I can try to export into SQL format, but there is another (much larger)
database that I need to migrate, and it has large binary blobs inside. I
would prefer the custom format.

Is the custom dump format of v11 compatible with v15?

Thank you,

   Laszlo

Re: ERROR: stack depth limit exceeded

[Laurenz Albe]

On Fri, 2023-09-08 at 11:32 +0800, gzh wrote:
> I'm sorry I didn't explain the issue clearly. 
> Our system is currently running on PostgreSQL 12.13 and we are planning to 
> upgrade to PostgreSQL 12.16. 
> We are currently in the evaluation phase. 
> In the Release Notes for PostgreSQL 12.14, we saw the following change:
> https://www.postgresql.org/docs/release/12.14/
> 
> > Add recursion and looping defenses in subquery pullup (Tom Lane)
> > 
> > A contrived query can result in deep recursion and unreasonable amounts of 
> > time spent trying to
> > flatten subqueries. A proper fix for that seems unduly invasive for a 
> > back-patch, but we can at
> > least add stack depth checks and an interrupt check to allow the query to 
> > be cancelled.
> 
> Our understanding is that this change will cause some complex SQL statements 
> that were previously not reporting errors to report errors in the new 
> version. 
> If our understanding of this change is correct, we would like to find out 
> which SQL statements will report errors in the new version. 
> Do you have any good methods for doing this?

Asking us to exhaustively describe all queries that could cause errors now
so that you can compare your queries with them is not a good way forward.
Please note that the release notes speak of "contrived queries", so you can
imagine things like

SELECT (
  SELECT (
SELECT (
   [1000 more such repetitions]
  SELECT 1
   [lots of closing parentheses]

Anyway, the queries that the fix makes throw errors in 12.14 are queries that
would also give you trouble in 12.13, and worse trouble than an error
("unreasonable amounts of time spent").

My advice it not to worry.

But if you feel like worrying very much, go ahead and test your application
thoroughly with 12.14.

Yours,
Laurenz Albe