Re: Adding SHOW CREATE TABLE

[Ron]

On 5/12/23 18:00, Kirk Wolak wrote:
[snip]

Where do we draw the lines?


At other tables.


Does Table DDL include all indexes?


Absolutely!

It should include constraints, clearly.  I would not think it should have 
triggers.


Definitely triggers.  And foreign keys.

Literally everything within the <>.  (ie, no ALTER 
.. OWNER TO...)




ALTER statements, too.  If CREATE TABLE ... LIKE ... { INCLUDING | EXCLUDING 
} { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | 
INDEXES | STATISTICS | STORAGE | ALL } can do it, then so should SHOW CREATE 
TABLE.


--
Born in Arizona, moved to Babylonia.

Re: Adding SHOW CREATE TABLE

[Stephen Frost]

Greetings,

* Kirk Wolak (wol...@gmail.com) wrote:
> On Fri, May 12, 2023 at 4:37 PM Stephen Frost  wrote:
> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > > Stephen Frost  writes:
> > > > Again, would be great to see someone actually work on this.  There's
> > > > already a good chunk of code in core in pg_dump and in the postgres_fdw
> > > > for doing exactly this and it'd be great to consolidate that and at the
> > > > same time expose it via SQL.
> > ...
> > No, it won't make sense to have yet another copy that's for the
> > currently-running-server-only, which is why I suggested it go into
> > either a common library or maybe into libpq.  I don't feel it would
> > be bad for the common code to have the multi-version understanding even
> > if the currently running backend will only ever have the option to ask
> > for the code path that matches its version.
> >
> Hmmm...What's wrong with only being for the currently running server?
> That's all I would expect.  Also, if it was there, it limits the
> expectations to DDL that
> works for that server version.

I didn't say anything was wrong with that, merely pointing out that
having the same set of code for these various use-cases would be better
than having multiple copies of it.  The existing code works just fine to
answer the question of "when on v15, what is the v15 query?", it just
happens to *also* answer "when on v15, what is the v14 query?" and we
need that already for postgres_fdw and for pg_dump.

> Also, if it's on the backend (or an extension), then it's available to
> everything.

I mean ... it's already in postgres_fdw, just not in a way that can be
returned to the user.  I don't think I'd want this functionality to
depend on postgres_fdw or generally on an extension though, it should
be part of core in some fashion.

> > Agreed- someone needs to have a fair bit of time and willingness to push
> > on this to make it happen.
> 
> If we can work through a CLEAR discussion of what it is, and is not.  I
> would be
> happy to work on this.  I like referencing the FDW.  I also thought of
> referencing
> the CREATE TABLE xyz(LIKE abc INCLUDING ALL).  While it's not doing DDL,
> it certainly has to be checking options, etc.  And pg_dump is the "gold
> standard".

I'd think the FDW code would be the best starting point, but, sure, look
at all the options.

> My approach would be to get a version working.  Then figure out how to
> generate "literally" all table options, and work the process.  The good news
> is that at a certain point the resulting DDL should be "comparable" against
> a ton of test tables.
> 
> Where do we draw the lines?  Does Table DDL include all indexes?
> It should include constraints, clearly.  I would not think it should have
> triggers.
> Literally everything within the <>.  (ie, no ALTER ..
> OWNER TO...)

I'd look at the IMPORT FOREIGN SCHEMA stuff in postgres_fdw.  We're
already largely answering these questions by what options that takes.
To some extent, the same is true of pg_dump, but at least postgres_fdw
is already backend code and probably a bit simpler than the pg_dump
code.  Still, looking at both would be a good idea.

> Next, I would want psql \st to simply call this?

Eh, that's an independent discussion and effort, especially because
people are possibly going to want that to generate the necessary ALTER
TABLE commands from the result and not just a DROP/CREATE TABLE.
 
> FWIW, we parse our pg_dump output, and store the objects as individual DDL
> files.
> So, I have about 1,000 tables to play with, for which I already know the
> DDL that pg_dump uses.

Sure.

> But it's a big commitment.  I don't mind if it has a reasonable chance of
> being accepted.

Yes, it's a large effort, no doubt.

> I accept that I will make a few mistakes (and learn) along the way.
> If there are ANY deal killers that would prevent a reasonable solution from
> being accepted, please let me know.

I don't think we can say one way or the other on this ...

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Adding SHOW CREATE TABLE

[Kirk Wolak]

On Fri, May 12, 2023 at 4:37 PM Stephen Frost  wrote:

> Greetings,
>
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > Stephen Frost  writes:
> > > Again, would be great to see someone actually work on this.  There's
> > > already a good chunk of code in core in pg_dump and in the postgres_fdw
> > > for doing exactly this and it'd be great to consolidate that and at the
> > > same time expose it via SQL.
> ...
> No, it won't make sense to have yet another copy that's for the
> currently-running-server-only, which is why I suggested it go into
> either a common library or maybe into libpq.  I don't feel it would
> be bad for the common code to have the multi-version understanding even
> if the currently running backend will only ever have the option to ask
> for the code path that matches its version.
>
> Hmmm...What's wrong with only being for the currently running server?
That's all I would expect.  Also, if it was there, it limits the
expectations to DDL that
works for that server version.

Also, if it's on the backend (or an extension), then it's available to
everything.


> Agreed- someone needs to have a fair bit of time and willingness to push
> on this to make it happen.
>

If we can work through a CLEAR discussion of what it is, and is not.  I
would be
happy to work on this.  I like referencing the FDW.  I also thought of
referencing
the CREATE TABLE xyz(LIKE abc INCLUDING ALL).  While it's not doing DDL,
it certainly has to be checking options, etc.  And pg_dump is the "gold
standard".

My approach would be to get a version working.  Then figure out how to
generate "literally" all table options, and work the process.  The good news
is that at a certain point the resulting DDL should be "comparable" against
a
ton of test tables.

Where do we draw the lines?  Does Table DDL include all indexes?
It should include constraints, clearly.  I would not think it should have
triggers.
Literally everything within the <>.  (ie, no ALTER ..
OWNER TO...)

Next, I would want psql \st to simply call this?

FWIW, we parse our pg_dump output, and store the objects as individual DDL
files.
So, I have about 1,000 tables to play with, for which I already know the
DDL that pg_dump uses.

But it's a big commitment.  I don't mind if it has a reasonable chance of
being accepted.
I accept that I will make a few mistakes (and learn) along the way.
If there are ANY deal killers that would prevent a reasonable solution from
being accepted,
please let me know.

Kirk...

Re: Death postgres

[Peter J. Holzer]

On 2023-05-12 17:41:37 +0200, Marc Millas wrote:
> On Fri, May 12, 2023 at 8:31 AM Peter J. Holzer  wrote:
> My guess is that the amount of parallelism is the problem.
> 
> work_mem is a per-node limit. Even a single process can use a multiple of
> work_mem if the query contains nested nodes (which almost every query
> does, but most nodes don't need much memory). With 5 parallel workers,
> the total consumption will be 5 times that. So to prevent the OOM
> condition you would need to reduce work_mem or max_parallel_workers (at
> least for this query).
> 
> 
> we have more than 100GB RAM and only 1 user, with one request running.
> work_mem is set to 10MB.

I don't remember mentioning you that before, so I had to guess.

>   for oom to kill due to work_mem it means that for one
> request with 2 left join, postgres needs more than 10.000 work_mem buffers.
> to me, it seems difficult to believe.

Yes. work_mem is unlikely to be the culprit here. It must be something
else which is eating the RAM. But I think it's hard to reproduce that
with the information you've given us this far.

> but that postgres may need that RAM space for hashing or whatever
> other similar purpose seems more probable. no ? 

That should adhere to the work_mem limit, too.

I'm not a core developer (actually not a Postgres developer at all, just
a developer of other stuff which happens to use Postgres), but I
remember that there was an issue some years ago where a hash table could
grow much larger than estimated by the planner. That issue was fixed,
but maybe you've run into a similar case?


> The description temp_file_limit says "...the maximum amount of disk
> space that *a process* can use...". So with 5 workers that's 210*5 =
> 1050 GB total. Again, you may want to reduce either temp_file_limit or
> max_parallel_workers.
> 
> Yes, but if so, we may have had a request canceled due to temp_file limit,
> which was not the case. 

Well, you've said that the disk space grew by "1.1 TB". I understand
that to mean "something between 1.05 TB and 1.15 TB", not exactly
11000 Bytes (or 1209462790554 Bytes or 1100213452800 Bytes,
depending on how you define "TB"). 1050 GB is within that range, so you
might just have missed that limit.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Adding SHOW CREATE TABLE

[Stephen Frost]

Greetings,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > Again, would be great to see someone actually work on this.  There's
> > already a good chunk of code in core in pg_dump and in the postgres_fdw
> > for doing exactly this and it'd be great to consolidate that and at the
> > same time expose it via SQL.
> 
> Note that this is hardly new ground: we've heard more-or-less the same
> proposal many times before.  I think the reason it's gone nowhere is
> that most of the existing infrastructure is either in pg_dump or designed
> to support pg_dump, and pg_dump is *extremely* opinionated about what
> it wants and how it wants the data sliced up, for very good reasons.
> Reconciling those requirements with a typical user's "just give me a
> reconstructed CREATE TABLE command" request seems fairly difficult.

Yet we're already duplicating much of this in postgres_fdw.  If we don't
want to get involved in pg_dump's feelings on the subject, we could look
to postgres_fdw's independent implementation which might be more
in-line with what users are expecting.  Having two separate copies of
code that does this and continuing to refuse to give users a way to ask
for it themselves seems at the least like an odd choice.

> Also, since pg_dump will still need to support old servers, it's hard
> to believe we'd accept any proposal to move that functionality into
> the server side, which in turn means that it's not going to be an easy
> SQL command.

No, it won't make sense to have yet another copy that's for the
currently-running-server-only, which is why I suggested it go into
either a common library or maybe into libpq.  I don't feel it would
be bad for the common code to have the multi-version understanding even
if the currently running backend will only ever have the option to ask
for the code path that matches its version.

> These issues probably could be surmounted with enough hard work, but
> please understand that just coming along with a request is not going
> to cause it to happen.  People have already done that.  (Searching
> the mailing list archives might be edifying.)

Agreed- someone needs to have a fair bit of time and willingness to push
on this to make it happen.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Adding SHOW CREATE TABLE

[Tom Lane]

Stephen Frost  writes:
> Again, would be great to see someone actually work on this.  There's
> already a good chunk of code in core in pg_dump and in the postgres_fdw
> for doing exactly this and it'd be great to consolidate that and at the
> same time expose it via SQL.

Note that this is hardly new ground: we've heard more-or-less the same
proposal many times before.  I think the reason it's gone nowhere is
that most of the existing infrastructure is either in pg_dump or designed
to support pg_dump, and pg_dump is *extremely* opinionated about what
it wants and how it wants the data sliced up, for very good reasons.
Reconciling those requirements with a typical user's "just give me a
reconstructed CREATE TABLE command" request seems fairly difficult.

Also, since pg_dump will still need to support old servers, it's hard
to believe we'd accept any proposal to move that functionality into
the server side, which in turn means that it's not going to be an easy
SQL command.

These issues probably could be surmounted with enough hard work, but
please understand that just coming along with a request is not going
to cause it to happen.  People have already done that.  (Searching
the mailing list archives might be edifying.)

regards, tom lane

Re: Adding SHOW CREATE TABLE

[Stephen Frost]

Greetings,

* Nathaniel Sabanski (sabansk...@gmail.com) wrote:
> > Perhaps a bit more discussion about what exactly the use-case is would
> > be helpful- what would you use this feature for?
> 
> App writers: To facilitate table creation and simplify schema verification,
> without relying on a GUI tool or ORM (or system calls out to pg_dump).

Not sure how it would simplify schema verification?

> Tool writers: Would drastically cut down the implementation time and
> complexity to support Postgres. I am one of the devs of Piccolo ORM (Python
> lib supporting Postgres) and we have a lot of code dedicated to
> re-generating the CREATE TABLE statements (creation, during migrations,
> etc) that could be done better by Postgres itself.

I'm curious- have you compared what you're doing to pg_dump's output?
Are you confident that there aren't any distinctions between those that,
for whatever reason, need to exist?

> Moving to Postgres: It would help ease migrations for developers wanting to
> move from MySQL / Percona / MariaDB to Postgres. Also it's a nice developer
> experience to see how Postgres generates X table without extra tooling.

Seems unlikely that this would actually be all that helpful there- tools
like ora2pg and similar know how to query other database systems and
write appropriate CREATE TABLE statements for PostgreSQL.

> The intention of SHOW CREATE TABLE is not to replace the existing suite of
> \d in psql but rather to be a developer friendly complement within SQL
> itself.

Sure, I get that.

Again, would be great to see someone actually work on this.  There's
already a good chunk of code in core in pg_dump and in the postgres_fdw
for doing exactly this and it'd be great to consolidate that and at the
same time expose it via SQL.

Another possible option would be to add this to libpq, which is used by
postgres_fdw, psql, pg_dump, and lots of other drivers and client
utilities.  If it's all broadly the same, personally I'd prefer it to be
in the common library and available as a backend SQL command too, but
perhaps there's reasons that it would be easier to implement in libpq
instead.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Adding SHOW CREATE TABLE

[Nathaniel Sabanski]

> Perhaps a bit more discussion about what exactly the use-case is would
> be helpful- what would you use this feature for?

App writers: To facilitate table creation and simplify schema verification,
without relying on a GUI tool or ORM (or system calls out to pg_dump).

Tool writers: Would drastically cut down the implementation time and
complexity to support Postgres. I am one of the devs of Piccolo ORM (Python
lib supporting Postgres) and we have a lot of code dedicated to
re-generating the CREATE TABLE statements (creation, during migrations,
etc) that could be done better by Postgres itself.

Ecosystem cohesion: SHOW CREATE TABLE has already been implemented in
CockroachDB, a popular Postgres derivative.

Moving to Postgres: It would help ease migrations for developers wanting to
move from MySQL / Percona / MariaDB to Postgres. Also it's a nice developer
experience to see how Postgres generates X table without extra tooling.

The intention of SHOW CREATE TABLE is not to replace the existing suite of
\d in psql but rather to be a developer friendly complement within SQL
itself.

Re: PG_Cron - Error Message Connection failed

[Adrian Klaver]

On 5/12/23 09:41, FOUTE K. Jaurès wrote:

Understand @Adrian Klaver  .
the log show:
2023-05-12 17:30:19.327 WAT [46190] LOG:  cron job 8 starting: SELECT 1
2023-05-12 17:30:19.339 WAT [46190] LOG:  cron job 8 connection failed


Well that did not go anywhere.

In your original post in the image under the database column it looks 
like, to my old eyes, there is a list of database names.


Is that the case?

Copy and paste that content in your reply.



Le ven. 12 mai 2023 à 17:23, Adrian Klaver > a écrit :


On 5/12/23 09:22, FOUTE K. Jaurès wrote:

1) Please do not top post. Use inline posting

2) Do not use images, copy and paste as text.

 > hello Fabricio,
 >
 > the listen_addresses is set to *
 > the result of psql command line
 > image.png
 > The job on pg_cron
 > image.png

What does the Postgres log show when pg_cron is trying to make
connections?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Jaurès FOUTE


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

Re: Adding SHOW CREATE TABLE

[Nathaniel Sabanski]

On Fri, 12 May 2023 at 09:12, David G. Johnston 
wrote:

>
>
> On Fri, May 12, 2023, 08:35 Thorsten Glaser  wrote:
>
>> On Fri, 12 May 2023, Nathaniel Sabanski wrote:
>>
>> >I believe most users would anticipate a CREATE TABLE statement that
>> aligns
>> >with the currently installed version- this is the practical solution for
>>
>> The currently installed version of what, the server or the client?
>>
>
> It's an SQL Command, no specific client can/should be presumed.
>
> David J.
>
>>

Re: Window function for get the last value to extend missing rows

[Thorsten Glaser]

On Fri, 12 May 2023, GF wrote:

>"The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead,
>lag, first_value, last_value, and nth_value. This is not implemented in
>PostgreSQL: the behavior is always the same as the standard's default,
>namely RESPECT NULLS".

Yeah, THAT caused no small amount of cursing, earlier this year,
I’d have also used IGNORE NULLS somewhere…

bye,
//mirabilos
-- 
15:41⎜ Somebody write a testsuite for helloworld :-)

Re: Window function for get the last value to extend missing rows

[GF]

On Fri, 12 May 2023 at 13:04, Durumdara  wrote:

> Dear Members!
>
> I have a table with temperature measures.
> The data is coming from the PLC, but sometimes the period is "slipping",
> so the values are not correctly minute based.
>
> 03:00 10
> 03:02 12
> 03:03 11
> 03:05 13
>
> I have to make a virtual table which is minute based.
>
> I thought I would make a generated temp table (generate_series) and then
> join these values based on minue.
>
> 03:00 10
> 03:01 NULL
> 03:02 12
> 03:03 11
> 03:04 NULL
> 03:05 13
>
> I need a code to replace the value to the last value on NULL.
>
> 03:00 10
> 03:01 10 <
> 03:02 12
> 03:03 11
> 03:04 11 <
> 03:05 13
>

Unfortunately, as per
https://www.postgresql.org/docs/current/functions-window.html
"The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead,
lag, first_value, last_value, and nth_value. This is not implemented in
PostgreSQL: the behavior is always the same as the standard's default,
namely RESPECT NULLS".

So, I'd keep the incoming data as is, i.e. with no nulls in values, and
densify it with some generate_series magic:
select gmin as mmin, d.value
from
( select mmin, lead(mmin) over (order by mmin) nextmin, value from
test_table ) d,
generate_series(d.mmin, nextmin - interval'1 minute') gmin

(I assumed a time representation for mmin, but adapt the generate_series
call to whatever your representation is)
Best,
g

Re: PG_Cron - Error Message Connection failed

[FOUTE K . Jaurès]

Understand @Adrian Klaver  .
the log show:
2023-05-12 17:30:19.327 WAT [46190] LOG:  cron job 8 starting: SELECT 1
2023-05-12 17:30:19.339 WAT [46190] LOG:  cron job 8 connection failed

Le ven. 12 mai 2023 à 17:23, Adrian Klaver  a
écrit :

> On 5/12/23 09:22, FOUTE K. Jaurès wrote:
>
> 1) Please do not top post. Use inline posting
>
> 2) Do not use images, copy and paste as text.
>
> > hello Fabricio,
> >
> > the listen_addresses is set to *
> > the result of psql command line
> > image.png
> > The job on pg_cron
> > image.png
>
> What does the Postgres log show when pg_cron is trying to make connections?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

-- 
Jaurès FOUTE

Re: PG_Cron - Error Message Connection failed

[Adrian Klaver]

On 5/12/23 09:22, FOUTE K. Jaurès wrote:

1) Please do not top post. Use inline posting

2) Do not use images, copy and paste as text.


hello Fabricio,

the listen_addresses is set to *
the result of psql command line
image.png
The job on pg_cron
image.png


What does the Postgres log show when pg_cron is trying to make connections?


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

Re: PG_Cron - Error Message Connection failed

[FOUTE K . Jaurès]

hello Fabricio,

the listen_addresses is set to *
the result of psql command line
[image: image.png]
The job on pg_cron
[image: image.png]

Le ven. 12 mai 2023 à 17:05, Fabricio Pedroso Jorge  a
écrit :

> Try checking the view cron.job, column "nodename", if it's set to 
> "localhost", change it (via an UPDATE) to the address defined in 
> "listen_address" parameter and see if this works.
>
>
> Em sex., 12 de mai. de 2023 às 17:00, FOUTE K. Jaurès <
> jauresfo...@gmail.com> escreveu:
>
>> Hello everyone,
>>
>> Can someone help me to understand this issue. I installed and configured
>> pg_cron on the server and I was able to connect without providing a
>> password using psql command line.
>> But when i schedule a job, like this: SELECT cron.schedule( 'TEST','30
>> seconds', $$SELECT 1$$);
>> the result show
>> [image: image.png]
>>
>> Any help is welcome
>>
>> --
>> Jaurès FOUTE
>>
>
>
> --
> *Fabrício Pedroso Jorge.*
> Database Architect / Database Engineer
>
> *LinkedIn Profile*
> http://br.linkedin.com/in/fabriciojorge
>
> *Contacts:*
> + 353 085 8221706
> fpjb...@gmail.com
>


-- 
Jaurès FOUTE

Re: Adding SHOW CREATE TABLE

[David G. Johnston]

On Fri, May 12, 2023, 08:35 Thorsten Glaser  wrote:

> On Fri, 12 May 2023, Nathaniel Sabanski wrote:
>
> >I believe most users would anticipate a CREATE TABLE statement that aligns
> >with the currently installed version- this is the practical solution for
>
> The currently installed version of what, the server or the client?
>

It's an SQL Command, no specific client can/should be presumed.

David J.

>

Re: PG_Cron - Error Message Connection failed

[Fabricio Pedroso Jorge]

Try checking the view cron.job, column "nodename", if it's set to
"localhost", change it (via an UPDATE) to the address defined in
"listen_address" parameter and see if this works.


Em sex., 12 de mai. de 2023 às 17:00, FOUTE K. Jaurès 
escreveu:

> Hello everyone,
>
> Can someone help me to understand this issue. I installed and configured
> pg_cron on the server and I was able to connect without providing a
> password using psql command line.
> But when i schedule a job, like this: SELECT cron.schedule( 'TEST','30
> seconds', $$SELECT 1$$);
> the result show
> [image: image.png]
>
> Any help is welcome
>
> --
> Jaurès FOUTE
>


-- 
*Fabrício Pedroso Jorge.*
Database Architect / Database Engineer

*LinkedIn Profile*
http://br.linkedin.com/in/fabriciojorge

*Contacts:*
+ 353 085 8221706
fpjb...@gmail.com

PG_Cron - Error Message Connection failed

[FOUTE K . Jaurès]

Hello everyone,

Can someone help me to understand this issue. I installed and configured
pg_cron on the server and I was able to connect without providing a
password using psql command line.
But when i schedule a job, like this: SELECT cron.schedule( 'TEST','30
seconds', $$SELECT 1$$);
the result show
[image: image.png]

Any help is welcome

-- 
Jaurès FOUTE

Re: Adding SHOW CREATE TABLE

[Thomas Kellerer]

Nathaniel Sabanski schrieb am 12.05.2023 um 13:29:


HN had a thread regarding the challenges faced by new users during
the adoption of Postgres in 2023.

One particular issue that garnered significant votes was the lack of
a "SHOW CREATE TABLE" command, and seems like it would be an easy one
to implement: https://news.ycombinator.com/item?id=35908991

Considering the popularity of this request and its potential ease of
implementation, I wanted to bring it to your attention, as it would
likely enhance the user experience and alleviate some of the
difficulties encountered by newcomers.

While it would be nice to have something like that, I don't think
it isn't really necessary. Pretty much every (GUI) SQL client provides
a way to see the DDL for objects in the database.

For psql fans \d typically is enough, and they would probably not mind
running pg_dump to get the full DDL.

I would think that especially newcomers start with a GUI client
that can do this.

If you check the source of any of the popular SQL clients that generates
the DDL for a table, then you will also quickly realize that this isn't
a trivial thing to do.

Thomas

Re: Death postgres

[Marc Millas]

On Fri, May 12, 2023 at 8:31 AM Peter J. Holzer  wrote:

> On 2023-05-11 21:27:57 +0200, Marc Millas wrote:
> > the 75 lines in each tables are not NULLs but '' empty varchar,
> which,
> > obviously is not the same thing.
> > and which perfectly generates 500 billions lines for the left join.
> > So, no planner or statistics pbs. apologies for the time wasted.
>
> No problem. Glad to have solved that puzzle.
>
> > Back to the initial pb: if, with temp_file_limit positioned to 210 GB,
> > I try to run the select * from table_a left join table_b on the col_a
> > (which contains the 75 '' on both tables)
> > then postgres do crash, killed by oom, after having taken 1.1 TB of
> additional
> > disk space.
>
> My guess is that the amount of parallelism is the problem.
>
> work_mem is a per-node limit. Even a single process can use a multiple of
> work_mem if the query contains nested nodes (which almost every query
> does, but most nodes don't need much memory). With 5 parallel workers,
> the total consumption will be 5 times that. So to prevent the OOM
> condition you would need to reduce work_mem or max_parallel_workers (at
> least for this query).
>

we have more than 100GB RAM and only 1 user, with one request running.
work_mem is set to 10MB.  for oom to kill due to work_mem it means that for
one request with 2 left join, postgres needs more than 10.000 work_mem
buffers.
to me, it seems difficult to believe. but that postgres may need that RAM
space for hashing or whatever other similar purpose seems more probable.
no ?

>
> The description temp_file_limit says "...the maximum amount of disk
> space that *a process* can use...". So with 5 workers that's 210*5 =
> 1050 GB total. Again, you may want to reduce either temp_file_limit or
> max_parallel_workers.
>
Yes, but if so, we may have had a request canceled due to temp_file limit,
which was not the case.

>
> > to my understanding, before postgres 13, hash aggregate did eat RAM
> limitless
> > in such circumstances.
> > but in 14.2 ??
> > (I know, 14.8 is up...)
>
> Maybe the older version of postgres didn't use as many workers for that
> query (or maybe not parallelize it at all)?
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>

Re: Adding SHOW CREATE TABLE

[Thorsten Glaser]

On Fri, 12 May 2023, Nathaniel Sabanski wrote:

>I believe most users would anticipate a CREATE TABLE statement that aligns
>with the currently installed version- this is the practical solution for

The currently installed version of what, the server or the client?

bye,
//mirabilos
-- 
15:41⎜ Somebody write a testsuite for helloworld :-)

Re: Adding SHOW CREATE TABLE

[Stephen Frost]

Greetings,

Please don't top-post on these lists.

* Nathaniel Sabanski (sabansk...@gmail.com) wrote:
> I believe most users would anticipate a CREATE TABLE statement that aligns
> with the currently installed version- this is the practical solution for
> the vast majority.

Perhaps a bit more discussion about what exactly the use-case is would
be helpful- what would you use this feature for?

> In situations where a CREATE TABLE statement compatible with an older
> version of Postgres is required, users can opt for an additional step of
> using tools like pg_dump or an older version of Postgres itself. This
> allows them to ensure compatibility without compromising the practicality
> of the process.

The issue is really both older and newer versions, not just older ones
and not just newer ones.

To the extent you're interested in this, I pointed out where you could
go look at the existing code as well as an idea for how to move this
forward.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Adding SHOW CREATE TABLE

[Nathaniel Sabanski]

I believe most users would anticipate a CREATE TABLE statement that aligns
with the currently installed version- this is the practical solution for
the vast majority.

In situations where a CREATE TABLE statement compatible with an older
version of Postgres is required, users can opt for an additional step of
using tools like pg_dump or an older version of Postgres itself. This
allows them to ensure compatibility without compromising the practicality
of the process.

On Fri, 12 May 2023 at 06:47, Stephen Frost  wrote:

> Greetings,
>
> * Nathaniel Sabanski (sabansk...@gmail.com) wrote:
> > HN had a thread regarding the challenges faced by new users during the
> > adoption of Postgres in 2023.
> >
> > One particular issue that garnered significant votes was the lack of a
> > "SHOW CREATE TABLE" command, and seems like it would be an easy one to
> > implement: https://news.ycombinator.com/item?id=35908991
> >
> > Considering the popularity of this request and its potential ease of
> > implementation, I wanted to bring it to your attention, as it would
> likely
> > enhance the user experience and alleviate some of the difficulties
> > encountered by newcomers.
>
> This isn't as easy as it seems actually ...
>
> Note that using pg_dump for this purpose works quite well and also works
> to address cross-version issues.  Consider that pg_dump v15 is able to
> connect to v14, v13, v12, v11, and more, and produce a CREATE TABLE
> command that will work with *v15*.  If you connected to a v14 database
> and did a SHOW CREATE TABLE, there's no guarantee that the CREATE TABLE
> statement returned would work for PG v15 due to keyword changes and
> other differences that can cause issues between major versions of PG.
>
> Now, that said, we have started ending up with some similar code between
> pg_dump and postgres_fdw in the form of IMPORT FOREIGN SCHEMA and maybe
> we should consider if that code could be moved into the common library
> and made available to pg_dump, postgres_fdw, and as a SHOW CREATE TABLE
> command with the caveat that the produced CREATE TABLE command may not
> work with newer versions of PG.  There's an interesting question around
> if we'd consider it a bug worthy of fixing if IMPORT FOREIGN SCHEMA in
> v14 doesn't work when connecting to a v15 PG instance.  Not sure if
> anyone's contemplated that.  There's certainly going to be cases that we
> wouldn't accept fixing (we wouldn't add some new partitioning strategy
> to v14 just because it's in v15, for example, to make IMPORT FOREIGN
> SCHEMA work...).
>
> Thanks,
>
> Stephen
>

Re: Adding SHOW CREATE TABLE

[Stephen Frost]

Greetings,

* Nathaniel Sabanski (sabansk...@gmail.com) wrote:
> HN had a thread regarding the challenges faced by new users during the
> adoption of Postgres in 2023.
> 
> One particular issue that garnered significant votes was the lack of a
> "SHOW CREATE TABLE" command, and seems like it would be an easy one to
> implement: https://news.ycombinator.com/item?id=35908991
> 
> Considering the popularity of this request and its potential ease of
> implementation, I wanted to bring it to your attention, as it would likely
> enhance the user experience and alleviate some of the difficulties
> encountered by newcomers.

This isn't as easy as it seems actually ... 

Note that using pg_dump for this purpose works quite well and also works
to address cross-version issues.  Consider that pg_dump v15 is able to
connect to v14, v13, v12, v11, and more, and produce a CREATE TABLE
command that will work with *v15*.  If you connected to a v14 database
and did a SHOW CREATE TABLE, there's no guarantee that the CREATE TABLE
statement returned would work for PG v15 due to keyword changes and
other differences that can cause issues between major versions of PG.

Now, that said, we have started ending up with some similar code between
pg_dump and postgres_fdw in the form of IMPORT FOREIGN SCHEMA and maybe
we should consider if that code could be moved into the common library
and made available to pg_dump, postgres_fdw, and as a SHOW CREATE TABLE
command with the caveat that the produced CREATE TABLE command may not
work with newer versions of PG.  There's an interesting question around
if we'd consider it a bug worthy of fixing if IMPORT FOREIGN SCHEMA in
v14 doesn't work when connecting to a v15 PG instance.  Not sure if
anyone's contemplated that.  There's certainly going to be cases that we
wouldn't accept fixing (we wouldn't add some new partitioning strategy
to v14 just because it's in v15, for example, to make IMPORT FOREIGN
SCHEMA work...).

Thanks,

Stephen


signature.asc
Description: PGP signature

Adding SHOW CREATE TABLE

[Nathaniel Sabanski]

HN had a thread regarding the challenges faced by new users during the
adoption of Postgres in 2023.

One particular issue that garnered significant votes was the lack of a
"SHOW CREATE TABLE" command, and seems like it would be an easy one to
implement: https://news.ycombinator.com/item?id=35908991

Considering the popularity of this request and its potential ease of
implementation, I wanted to bring it to your attention, as it would likely
enhance the user experience and alleviate some of the difficulties
encountered by newcomers.

Window function for get the last value to extend missing rows

[Durumdara]

Dear Members!

I have a table with temperature measures.
The data is coming from the PLC, but sometimes the period is "slipping", so
the values are not correctly minute based.

03:00 10
03:02 12
03:03 11
03:05 13

I have to make a virtual table which is minute based.

I thought I would make a generated temp table (generate_series) and then
join these values based on minue.

03:00 10
03:01 NULL
03:02 12
03:03 11
03:04 NULL
03:05 13

I need a code to replace the value to the last value on NULL.

03:00 10
03:01 10 <
03:02 12
03:03 11
03:04 11 <
03:05 13

The LAG function seems to be ok, but how to handle if more than two periods
are missing?

03:00 10
03:01 NULL
03:02 NULL
03:03 NULL
03:04 11
03:05 13

select *, coalesce(value, prev_value) as value from (
select mmin, value,
lag(value, 1) over (order by mmin) as prev_value
from test_table) t

or

select *, coalesce(value, prev_value) as value from (
select mmin, value,
coalesce(lag(value, 1) over (order by mmin),
lag(value, 2) over (order by mmin)) as prev_value
from tmp_test_table) t

The test data:

create table tmp_test_table (mmin int, value int);
insert into tmp_test_table values
(1, 1),
(2, 1),
(3, NULL),
(4, NULL),
(5, 2),
(6, NULL),
(7, NULL),
(10, 10),
(11, NULL),
(12, NULL),
(13, NULL),
(14, NULL);


The result is:

1 1 NULL 1
2 1 1 1
3 NULL 1 1
4 NULL 1 1
5 2 NULL 2
6 NULL 2 2
7 NULL 2 2
10 10 NULL 10
11 NULL 10 10
12 NULL 10 10
13 NULL NULL NULL
14 NULL NULL NULL

So you can see, the last values are NULL because the LAG can't use the last
calculated value.

Do you have any idea how to get the last value, doesn't matter how many
NULL-s are in the set?

(15, NULLx20, 10) => 15x21, 10

Thanks

Best regards
dd

Re: Death postgres

[Peter J. Holzer]

On 2023-05-11 21:27:57 +0200, Marc Millas wrote:
> the 75 lines in each tables are not NULLs but '' empty varchar, which,
> obviously is not the same thing.
> and which perfectly generates 500 billions lines for the left join.
> So, no planner or statistics pbs. apologies for the time wasted.

No problem. Glad to have solved that puzzle.

> Back to the initial pb: if, with temp_file_limit positioned to 210 GB,
> I try to run the select * from table_a left join table_b on the col_a
> (which contains the 75 '' on both tables)
> then postgres do crash, killed by oom, after having taken 1.1 TB of additional
> disk space. 

My guess is that the amount of parallelism is the problem.

work_mem is a per-node limit. Even a single process can use a multiple of
work_mem if the query contains nested nodes (which almost every query
does, but most nodes don't need much memory). With 5 parallel workers,
the total consumption will be 5 times that. So to prevent the OOM
condition you would need to reduce work_mem or max_parallel_workers (at
least for this query).

The description temp_file_limit says "...the maximum amount of disk
space that *a process* can use...". So with 5 workers that's 210*5 =
1050 GB total. Again, you may want to reduce either temp_file_limit or
max_parallel_workers.

> to my understanding, before postgres 13, hash aggregate did eat RAM limitless
> in such circumstances.
> but in 14.2 ??
> (I know, 14.8 is up...)

Maybe the older version of postgres didn't use as many workers for that
query (or maybe not parallelize it at all)?

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature