Re: Creating files with testdata

[H]

On March 10, 2025 5:09:46 PM GMT-04:00, H  wrote:
>On March 10, 2025 3:22:41 PM GMT-04:00, Francisco Olarte
> wrote:
>>On Mon, 10 Mar 2025 at 19:17, H  wrote:
>>...
>>> After entering my test data into the markdown file for the given
>test
>>scenario, I would then run an awk script or similar to create a SQL
>>file with the various CTE INSERT statements. Howevever, it gets
>complex
>>since I need to handle 1:N relationships between tables in the
>markdown
>>file...
>>> I hope the above outline is understandable and am interested in
>>comments and thoughts on my above approach.
>>
>>I do not think MD would be a good source format. Being a developer I
>>would recommend taking your favorite scripting language ( perl /
>>python / lua / js , whatever ), build a big object ( which can be
>>easily helped by some mini-functions to build a little DSL ) and then
>>spit sql from it ( for developemewnt it is normally better then using
>>whatever db connection your language has, as it leaves an
>>understandable sql script ).
>>
>>I have done this with perl for some projects, built a driver which
>>defined several helper functions, then dofile("xx.dat") which returned
>>a big hash and then a series of loops on the result to write the SQL
>>in whatever order was neccessary.
>>
>>Francisco Olarte.
>
>The plan is to use the same format for later adding information into
>the finished application. By the way, all information is text, either
>paragraphs, sentences or single words.

I am also contemplating using Pandoc to process the markdown file. Pandoc 
builds an AST which can then be processed as desired. Awk processes a file 
line-by-line which is not ideal.

Creating files with testdata

[H]

I am developing a complex multi-tenant application in postgresql 16 in Linux. 
During the development I would like to be able to enter test data into various 
related tables at any given time for testing purposes.

While this can certainly be accomplished by predefined CTE INSERT statements in 
an SQL file, I would prefer something more flexible. I am thinking of using a 
markdown file as a source for these CTE INSERT statements. Specifically, having 
a certain structure in the markdown file where a given markdown heading level, 
bullet level etc. would correspond to specific tables and columns.

After entering my test data into the markdown file for the given test scenario, 
I would then run an awk script or similar to create a SQL file with the various 
CTE INSERT statements. Howevever, it gets complex since I need to handle 1:N 
relationships between tables in the markdown file...

I hope the above outline is understandable and am interested in comments and 
thoughts on my above approach.

Thanks.

Re: [EXTERNAL] Re: Asking for OK for a nasty trick to resolve PG CVE-2025-1094 i

[Abraham, Danny]

Explanation.
We have hundreds of pg servers (mainly linux).
App is 7×24.
We think that patching the server to 15.12.will cost about 30 times more 
compared to patching the pg client ( mainly qa effort).
The app working fine using [libpq, psql] on both Linux as well as Windows.
Would love to hear your opinion.
Thanks
Danny


Sent from Workspace ONE Boxer

On Mar 6, 2025 10:11, Laurenz Albe  wrote:
[redirecting to pgsql-general]

On Thu, 2025-03-06 at 07:39 +, Abraham, Danny wrote:
> I have many customers using PG 15.3 happily, and I cannot just snap upgrade 
> them all to 15.12.

Why do you think you cannot do that?
In the long run, you'll be sorry if you don't.
It is just a matter of replacing the software and restarting the database 
server.

> I have tested a nasty trick of replacing PSQL,LIBPQ and several other DLL's 
> so that
> I have a PG client 15.12 within the folders of Server 15.3.
>
> All working just fine.
>
> I plan to ship it as a patch - but would like to hear you opinion on this 
> "merge".
>
> (Of course, the next version will use PG 17.4, so this is just an SOS action).
>
> Directory of C:\Users\dbauser\Desktop\15.12
>
> 02/20/2025  11:48 AM 4,696,576 libcrypto-3-x64.dll
> 02/20/2025  11:48 AM 1,850,401 libiconv-2.dll
> 02/20/2025  11:48 AM   475,769 libintl-9.dll
> 02/20/2025  11:48 AM   323,584 libpq.dll
> 02/20/2025  11:48 AM   779,776 libssl-3-x64.dll
> 02/20/2025  11:48 AM52,736 libwinpthread-1.dll
> 02/20/2025  11:48 AM   604,160 psql.exe
>
> ==
> C:\Program Files\BMC Software\Control-M Server\pgsql\bin>postgres -V
> postgres (PostgreSQL) 15.3
>
> C:\Program Files\BMC Software\Control-M Server\pgsql\bin>psql -V
> psql (PostgreSQL) 15.12

There is nothing fundamentally evil about upgrading the client.

But what is the point?  Why are you worried about client bugs more than
about server bugs?  The latter are much more likely to eat your data.

But then, if you are using Windows, perhaps you don't care a lot about
your data...

Yours,
Laurenz Albe

Re: Duplicate Key Values

[mark bradley]

Adrian & Ron,

Thank you for your assistance.

Best regards,
Mark Brady
amazon.com/author/markjbrady

From: Adrian Klaver 
Sent: Thursday, March 13, 2025 1:03 PM
To: mark bradley ; Ron Johnson 
; pgsql-general 
Subject: Re: Duplicate Key Values

On 3/13/25 09:25, mark bradley wrote:
>
>  > I'm assuming that by 'simple version' you mean no inheritance.
>
> Inheritance was not specified by me in either case.  By simple, I mean

Unless someone else is working on this code, it was done by you.

Pretty sure it had to do with from this message:

https://www.postgresql.org/message-id/SJ2PR22MB4328CEB1B47FC1AC4A996CB3BAD12%40SJ2PR22MB4328.namprd22.prod.outlook.com

"Although I did not explicitly use Postgres to declare inheritance,
logically speaking table dataset and processing _node inherit or are
subclasses of node because they are subclasses of node in a dataflow
diagram."

I'm guessing you actually did do the subclassing(inheritance) as it was
an option presented in the pgAdmin4 CREATE TABLE screen.

Anyway at this point the problem has been identified and a solution devised.



> that I used fewer columns in the test version, like so:
>
>
>
> Where *node_id* is a foreign key in dataset, and *node_type* is not.
>
> Best regards,
> Mark Brady


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

Re: The logic behind comparing generic vs. custom plan costs

[David G. Johnston]

On Sat, Mar 15, 2025 at 10:42 AM Justin Blank 
wrote:

> My idea had been that even if the custom plans average higher cost
> than the generic plan, it is still worth considering custom plans. If
> 1 time in 5, the custom plan is 10% of the cost of the generic plan,
> it doesn't matter if the average custom plan is worse, you should at
> least determine the cost of the custom plan before deciding whether to
> use it or the generic plan. Even if 80% of the time, you don't use the
> custom plan, the payoff in the good case may be worth it.
>
>
In theory, while ignoring planning costs, a generic plan will never
outperform a custom plan (the custom plan establishes a floor any custom
plan could fall back on).  Thus any algorithm that requires computing the
custom plan unconditionally amounts to simply setting the GUC to infinity
(instead of 5).

The assumption is that at moderate to high reuse volumes it is quite
probable that a generic plan will win or at least be acceptable in 999 in
1000 or more executions, not 4 in 5.

David J.

Re: The logic behind comparing generic vs. custom plan costs

[Tom Lane]

Justin Blank  writes:
> My idea had been that even if the custom plans average higher cost
> than the generic plan, it is still worth considering custom plans. If
> 1 time in 5, the custom plan is 10% of the cost of the generic plan,
> it doesn't matter if the average custom plan is worse, you should at
> least determine the cost of the custom plan before deciding whether to
> use it or the generic plan. Even if 80% of the time, you don't use the
> custom plan, the payoff in the good case may be worth it.

What you'd have to argue is that the average cost of the custom plans
(including the planning time) is less than the average cost of the
generic plan (including planning time to make a custom plan that's
then not used).  I'm not exactly convinced that that's any better
than "always use a custom plan" mode: once you've gone to the
trouble of making a custom plan, why not use it?

I'd be the first to agree that this logic is squishy as heck, and
I'd love to find a better way.  There are a couple of very nasty
problems to think about, though:

* The estimate of the cost of planning is pretty laughable.  It's
not a very detailed estimate, and even if it were there's a scale
problem in comparing it to estimated execution costs.  So I'm
hesitant to put any great weight on that estimate.

* Even comparing the estimated costs of custom and generic plans
is full of pitfalls, because they are based on different estimates
of the selectivity of WHERE clauses.  We've had reports for instance
of the cache picking a generic plan that was awful for the actual
parameter values, because the generic plan's cost was based on an
assumed-average case, while the custom plans' cost reflected reality
more accurately and hence were higher than the generic estimate even
though in reality they were the better choice.  (I don't recall
precise details, but I think it was something like the actual
parameter matching a very common value so that the number of rows
involved would be much greater than the generic plan assumed.)

Without some way to tame those problems, I'm not sure that marginal
tweaks in the plan-selection rule will accomplish a lot.

One idea perhaps could be to pay attention to how much the custom
plans' estimated rowcounts move around, and only switch to a generic
plan if there's not too much change and the generic plan's estimated
rowcounts are in the same range.  Or in other words, rather than
believing a cost comparison, what we'd look for is whether we get
basically the same plan in generic mode as in custom mode.  I'm
not sure how to mechanize that, though.

regards, tom lane

The logic behind comparing generic vs. custom plan costs

[Justin Blank]

I've been looking into the way that postgres decides whether to use a
custom or generic plan. I believe I understand how, but I have
confused myself about the why.

In plancache.c, the code always executes a custom plan for the first
five iterations, and afterwards, it compares the average cost of the
custom plan (including the planning cost) to the cost of a generic
plan (not including planning), and choose the option with the lower
cost.

My idea had been that even if the custom plans average higher cost
than the generic plan, it is still worth considering custom plans. If
1 time in 5, the custom plan is 10% of the cost of the generic plan,
it doesn't matter if the average custom plan is worse, you should at
least determine the cost of the custom plan before deciding whether to
use it or the generic plan. Even if 80% of the time, you don't use the
custom plan, the payoff in the good case may be worth it.

However, after starting to implement that idea, I became confused, and
the underlying logic of comparing custom and generic plans stopped
making sense to me.

I can think of two different reasons the cost estimates for the
generic plan and custom plan can differ:

1. The two plans substantively differ: they use a different join
   order, different join strategy, or different indexes.
2. They do not fundamentally differ, but the custom plan makes
   different estimates of the selectivity of predicates because it
   knows their precise values.

For the sake of deciding between custom and generic plans, it seems
like only the first type of difference matters.

So my first question is whether there's any value in comparing the
cost estimates for custom plans that have the same access plan as the
generic plan.

Continuing to cases where the plans differ meaningfully, I'm not sure
that comparison makes sense either.

If the custom plan beats the generic plan, it seems reasonable to take
that as a real improvement.

However, if the custom plan has a higher cost than the generic plan,
this may or may not count against the custom plan. The planner used
the bind parameters of the specific query. So it may be that the
custom plan really is worse (the planner has made a mistake) but it
may also be that it considered the access plan of the generic plan,
and it was worse for those particular bind parameters. In this case,
the custom plan appears worse than the generic plan, but would ideally
be preferred.

Does my reasoning make sense? Or have I misunderstood something about
this process and how to reason about these cost estimates? I'm very
unsure, and worrying I've completely misunderstood, as if I'm right,
the logic in plancache.c doesn't make much sense.

Justin Blank

After upgrading libpq, the same function(PQftype) call returns a different OID

[M Tarkeshwar Rao]

Hi all,

We are using PostgreSQL libpq in our application. The code worked fine for the 
past four years, but after upgrading the library, the function PQftype is 
returning unexpected values for some columns.
Specifically, the issue occurs with a column of type timestamp(3) without time 
zone.

What could be causing this change, and how can we resolve it?"

Postgres Version(upgraded from 11.18 to 13.20)

Example:

Before the libpq Upgrade (Expected Behavior)

Let's say your PostgreSQL database has a table:

CREATE TABLE example (
id SERIAL PRIMARY KEY,
filetime TIMESTAMP(3) WITHOUT TIME ZONE
);

In the old version of libpq, calling PQftype on the filetime column returns:

Oid filetime_oid = PQftype(res, 1); // Assuming 'filetime' is at index 1
printf("Filetime column OID: %u\n", filetime_oid);

Expected output (before upgrade):
Filetime column OID: 1114

After the libpq Upgrade (Unexpected Behavior)
After upgrading libpq, the same function call returns a different OID, such as 
123456.

What could be the reason?

Regards
Tarkeshwar

Re: After upgrading libpq, the same function(PQftype) call returns a different OID

[Tom Lane]

M Tarkeshwar Rao  writes:
> We are using PostgreSQL libpq in our application. The code worked fine for 
> the past four years, but after upgrading the library, the function PQftype is 
> returning unexpected values for some columns.
> Specifically, the issue occurs with a column of type timestamp(3) without 
> time zone.

The OID of type timestamp has not changed.  Perhaps you are now
querying some other table.  I'd suggest looking into pg_type to
find out what type is represented by the OID you're now getting,
and then searching pg_attribute to see what's using that.

select typname from pg_type where oid = 123456;

select attrelid::regclass, attname from pg_attribute where atttypid = 123456;

Also, if you really do mean that you changed only libpq and
nothing about the server side, I'd have to guess that you're
connecting to some other database than before.  That would be
surprising, but with zero details it's hard to debug.

regards, tom lane