Re: Adding SHOW CREATE TABLE

[Kirk Wolak]

On Sat, May 13, 2023 at 1:03 AM Ron  wrote:

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

I can see the ALTER statements now.  Which is why I asked.
I don't like the idea of including the trigger DDL, because that would
never execute in a clean environment.
(I've never used a tool that tried to do that when I've wanted the DDL)
I can go either way on index creation.

Does this imply SQL SYNTAX like:

SHOW CREATE TABLE 
  [ INCLUDING { ALL | INDEXES |  SEQUENCES | ??? }]
  [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ]
  [FOR {V11 | V12 | V13 | V14 | V15 }] ??
?

The goal for me  is to open the discussion, and then CONSTRAIN the focus.

Personally, the simple syntax:
SHOW CREATE TABLE table1;

Should give me a create table command with the table attributes and the
column attributes, FKs, PKs, Defaults.  Etc.
But I would not expect it to generate index commands, etc.

Re: PG_Cron - Error Message Connection failed

[FOUTE K . Jaurès]

Le ven. 12 mai 2023 à 20:21, Adrian Klaver  a
écrit :

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

Hello @Adrian Klaver  sorry for the delais.
Copy of content of cron.job ?


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

-- 
Jaurès FOUTE

Re: Adding SHOW CREATE TABLE

[Kirk Wolak]

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

> Greetings,
> ..
> 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.
>

I will start with postgres_fdw then, but gladly review the other source...
Just thinking about the essence of the syntax.
SHOW CREATE TABLE abc(LIKE real_table); -- Output CREATE TABLE abc();
using real_table?


I'd look at the IMPORT FOREIGN SCHEMA stuff in postgres_fdw.  We're
> already largely answering these questions by what options that takes.
>

Will do.

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

At least there is a base of code to start with.
I see a strong need to come up with a shell script to that could:

FOR  DO
  psql -c "SHOW... \g | cat >  "
  pg_dump --  only | remove_comments_normalize  | cat

  DIFF  

Of course, since our tests are usually all perl, a perl version.
But I would clearly want some heavy testing/validation.

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

[Kirk Wolak]

On Sat, May 13, 2023 at 2:18 AM Andrew Gierth 
wrote:

> > "Durumdara" == Durumdara   writes:
>
> create table tmp_test_table(mmin,val)
>as select o, v
> from unnest(array[1,5,NULL,3,NULL,NULL,10,7,NULL,NULL,NULL,4])
>with ordinality as u(v,o);
> select * from tmp_test_table order by mmin;
>


That seems like a lot of work.
If you have ALL the values (no missing values) a simple CTE handles this:

https://www.db-fiddle.com/f/wKyQV1imGsewR9Az7hi193/0

WITH RECURSIVE rec_cte(mmin, value) AS (
SELECT mmin, value from tmp_test_table where mmin=1
UNION ALL
SELECT t.mmin, COALESCE(t.value,r.value)
  FROM tmp_test_table t, rec_cte r WHERE r.mmin=(t.mmin-1)
)
SELECT * from rec_cte order by mmin;

Re: PG_Cron - Error Message Connection failed

[Adrian Klaver]

On 5/13/23 00:54, FOUTE K. Jaurès wrote:







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.


Hello @Adrian Klaver  sorry for the 
delais.

Copy of content of cron.job ?


The text version of whatever was in the image in this post:

https://www.postgresql.org/message-id/CAHQ1jffWF7Y8c1X7EK3JvbLJgw1GEcVk0uPa3%2B0CJo4h8PFHVw%40mail.gmail.com

Or at least what was in the database column.


--
Jaurès FOUTE


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

Re: order by

[Marc Millas]

On Thu, May 11, 2023 at 11:08 PM Ron  wrote:

> On 5/11/23 09:55, Marc Millas wrote:
>
> Thanks,
>
> I do know about index options.
>
> that table have NO (zero) indexes.
>
>
> If the table has no indices, then why did you write "it looks like there
> is something different within the *b-tree operator* class of varchar"?
> After all, you only care about b-trees when you have b-tree indices.
>
> to my understanding, the btree operator is the default operator used to do
any sort, like an order by, for varchar, text, .. types.

> --
> Born in Arizona, moved to Babylonia.
>

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

[postmaster]

Am 12.05.23 um 13:04 schrieb Durumdara:

[...]

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

[...]

and how do you think about NULL in first(and second/third) row?

Re: Adding SHOW CREATE TABLE

[Ron]

On 5/13/23 02:25, Kirk Wolak wrote:

On Sat, May 13, 2023 at 1:03 AM Ron  wrote:

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.



I can see the ALTER statements now.  Which is why I asked.
I don't like the idea of including the trigger DDL, because that would 
never execute in a clean environment.


I would not be grumpy if trigger statements weren't included.


(I've never used a tool that tried to do that when I've wanted the DDL)
I can go either way on index creation.

Does this imply SQL SYNTAX like:

SHOW CREATE TABLE 
  [ INCLUDING { ALL | INDEXES |  SEQUENCES | ??? }]
  [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ]
  [FOR {V11 | V12 | V13 | V14 | V15 }] ??
?


"FOR {V...}" is a complication too far, IMO.  No one expects "pg_dump 
--schema-only" to have a --version= option, so one should not expect SHOW 
CREATE TABLE to have a "FOR {V...}" clause.



The goal for me  is to open the discussion, and then CONSTRAIN the focus.

Personally, the simple syntax:
SHOW CREATE TABLE table1;

Should give me a create table command with the table attributes and the 
column attributes, FKs, PKs, Defaults. Etc.

But I would not expect it to generate index commands, etc.


--
Born in Arizona, moved to Babylonia.

Re: Adding SHOW CREATE TABLE

[Jeremy Smith]

On Sat, May 13, 2023, 3:25 AM Kirk Wolak  wrote:

> Does this imply SQL SYNTAX like:
>
> SHOW CREATE TABLE 
>   [ INCLUDING { ALL | INDEXES |  SEQUENCES | ??? }]
>   [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ]
>   [FOR {V11 | V12 | V13 | V14 | V15 }] ??
> ?
>

Personally, I would expect a function, like pg_get_tabledef(oid), to match
the other pg_get_*def functions instead of overloading SHOW.  To me, this
also argues that we shouldn't include indexes because we already have a
pg_get_indexdef function.

  -Jeremy

>
>

Re: Adding SHOW CREATE TABLE

[Kirk Wolak]

On Sat, May 13, 2023 at 3:34 PM Jeremy Smith  wrote:

>
>
> On Sat, May 13, 2023, 3:25 AM Kirk Wolak  wrote:
>
>> Does this imply SQL SYNTAX like:
>>
>> SHOW CREATE TABLE 
>>   [ INCLUDING { ALL | INDEXES |  SEQUENCES | ??? }]
>>   [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ]
>>   [FOR {V11 | V12 | V13 | V14 | V15 }] ??
>> ?
>>
>
> Personally, I would expect a function, like pg_get_tabledef(oid), to match
> the other pg_get_*def functions instead of overloading SHOW.  To me, this
> also argues that we shouldn't include indexes because we already have a
> pg_get_indexdef function.
>
>   -Jeremy
>
+1

In fact, making it a function will make my life easier for testing, that's
for certain.  I don't need to involve the parser,etc.  Others can help with
that after the function works.
Thanks for the suggestion!

stop

[Rahul Chordiya]

unsub