pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Achilleas Mantzios - cloud]

Hello

I notice both my kids struggling with either C or Python as first 
programming languages. I believe both are unsuitable for use as 
introductory languages to college juniors.


Python IMHO is too advanced, too rich, weird indentation rules, no 
simple for loop etc.


C, ok, punishing little kids with segmentation faults, calling by value 
VS by reference and 3ple pointers is pure sadism.


So this brings me to memory good old PASCAL from the time I was junior, 
circa 1986. PL/SQL resembles PASCAL heavily. Everything seems well 
defined, strong typing, simplicity, I think it has everything a 
programming language should have in order to be taught as an 
introductory language. But it lacks IO and file handling.


So, I ask, have there been any efforts to bring PL/PGSQL to the terminal?

Thanks!

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Karsten Hilbert]

Am Thu, Mar 07, 2024 at 04:56:18PM +0200 schrieb Achilleas Mantzios - cloud:

> Python IMHO is too advanced, too rich,

Python _is_ powerful but it needn't be difficult.

> weird indentation rules

A matter of taste IMO.

> no simple for loop

Really ?

Python 3.11.2 (main, Mar 13 2023, 12:18:29) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> for idx in [0,1,2,3]: print(idx)
...
0
1
2
3
>>>

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: v11.5- v15.3 upgrade (linux)

[Ron Johnson]

Bug fixes for problems (like slow queries) you didn't realize you had, or
would have if you later implemented a feature with the bug.  (For example,
an aggravating weekly manual activity disappeared when I was allowed to
update from 9.6.6 to 9.6.24.)

And, of course, security bug fixes,  Won't you have egg on your face when
it turns out that black hats stole data because of a bug fixed in a later
release?

On Thu, Mar 7, 2024 at 10:49 AM David Gauthier 
wrote:

> Because 15.3 is the latest they have installed at this time.  I could
> petition to get something even more recent.  But it would only make sense
> if it's a significant win and not just options/features that we don't use.
>
> On Thu, Mar 7, 2024 at 12:53 AM Ron Johnson 
> wrote:
>
>> On Wed, Mar 6, 2024 at 7:19 PM David Gauthier 
>> wrote:
>>
>>> Hi:
>>> I'm a PG user in a big corp with an IT dept that administers a PG
>>> server/instance that I use.  It's an old install, v11.5, and we need to
>>> upgrade to v15.3.  They want to bring the upgraded DB up on a new linux vm
>>> which has OS upgrades of its own.  So it's a move AND an upgrade.
>>>
>>
>> Have them upgrade the current server to 11*.22*.  It's trivially easy,
>> with only a few minutes of down time.
>>
>> Remember (and tell your IT dept) that PG point releases do not add new
>> features: only bug fixes.  That means point releases aren't nearly as risky
>> as other products.
>>
>>

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Christophe Pettus]

> On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud 
>  wrote:
> So, I ask, have there been any efforts to bring PL/PGSQL to the terminal?

Strictly speaking, of course, you can use PL/pgSQL from the terminal already: 
just use psql, connect to the database, and create and run functions and 
procedures as much as you like.

If the question is, "Have there been any efforts to implement a PL/pgSQL 
interpreter without PostgreSQL?", that's a different and much more complex 
problem.  PL/pgSQL uses the PostgreSQL query execution machinery to run pretty 
much anything that is not a control structure, and the language is very focused 
on interacting with the database.  I doubt it would be worth anyone's time to 
try to build some kind of minimal framework that implements the SPI to allow 
PL/pgSQL to operate without PostgreSQL.

Re: v11.5- v15.3 upgrade (linux)

[Adrian Klaver]

On 3/7/24 07:47, David Gauthier wrote:
Please reply to list also.
Ccing list


 >>I would definitely read the release notes for 12.0, 13.0, 14.0 and 15.0.
Check !  None of the breaking changes affect me.




 >>How are and from where are you installing the Postgres package(s)?
IT does the installs.  I'd have to check with them.
What would you recommend ? (again, linux installs)


I would use the packaging system for whatever Linux distro you are on. 
In particular, if available, the community repos from here:


https://www.postgresql.org/download/

They generally offer more choice of Postgres versions for a given distro 
version.




 >>.The would be plan 2 if installing  the plperlu package is not possible
We do not want or need plperlu on the 15.3 DB


Then you can skip installing that package, assuming you have cleared out 
all references to plperlu in the 11 instance.




 >> I would backup the 11.5 instance...
Absolutely.  In fact, IT was planning on doing an 11.5 backup and 
restore on 15.3.  Unsure if restoring an 11.5 into a 15.3 is a wise 
approach.  Any thoughts on that?


According to item below that was already tested and worked. Is that correct?



 >>The above is going to depend on the size of the database
Relative to what PG can do, this one is small (a few million 
records/table tops).  No partitioning.


 >>If you have the ability to do a test migration on a test machine 
where oops are not a problem that would be way to verify the 2) and 3) 
options.

Already done.  He restored the 11.5 on 15.3 and I tested it.  Works fine.


So good to go, depending on extent of testing.



I don't think I'll be able to sell the "3" option above to the IT 
group.  I think they'll want to do something simpler and with fewer steps.

So the question is... option 1 or 2 ?  Or does it matter, either can work ?



If it where me it would be 1) as that gives you a fallback provision to 
the original 11 instance.



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

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Pavel Stehule]

čt 7. 3. 2024 v 16:59 odesílatel Christophe Pettus 
napsal:

>
>
> > On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud <
> a.mantz...@cloud.gatewaynet.com> wrote:
> > So, I ask, have there been any efforts to bring PL/PGSQL to the terminal?
>
> Strictly speaking, of course, you can use PL/pgSQL from the terminal
> already: just use psql, connect to the database, and create and run
> functions and procedures as much as you like.
>
> If the question is, "Have there been any efforts to implement a PL/pgSQL
> interpreter without PostgreSQL?", that's a different and much more complex
> problem.  PL/pgSQL uses the PostgreSQL query execution machinery to run
> pretty much anything that is not a control structure, and the language is
> very focused on interacting with the database.  I doubt it would be worth
> anyone's time to try to build some kind of minimal framework that
> implements the SPI to allow PL/pgSQL to operate without PostgreSQL.
>

yes

plpgsql cannot exist without Postgres. PL/pgSQL is strongly reduced
interpreted Ada language. The gcc compiler supports Ada language.

I found https://bush.sourceforge.net/bushref.html - it is interpret with
Ada syntax, but it is better to learn Python - it is easy - with a pretty
big library.

free pascal https://www.freepascal.org/ is good compiler and you can write
terminal applications too - with Turbo Vision

Regards

Pavel

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Adrian Klaver]

On 3/7/24 06:56, Achilleas Mantzios - cloud wrote:

Hello

I notice both my kids struggling with either C or Python as first 
programming languages. I believe both are unsuitable for use as 
introductory languages to college juniors.


What the cool kids are using:)?

https://www.rust-lang.org/

https://doc.rust-lang.org/rust-by-example/

This old non-professional programmer could see learning this, so young 
minds should be able to grasp it.




Python IMHO is too advanced, too rich, weird indentation rules, no 
simple for loop etc.


C, ok, punishing little kids with segmentation faults, calling by value 
VS by reference and 3ple pointers is pure sadism.


So this brings me to memory good old PASCAL from the time I was junior, 
circa 1986. PL/SQL resembles PASCAL heavily. Everything seems well 
defined, strong typing, simplicity, I think it has everything a 
programming language should have in order to be taught as an 
introductory language. But it lacks IO and file handling.


So, I ask, have there been any efforts to bring PL/PGSQL to the terminal?

Thanks!





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

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Markur Sens]

> On 7 Mar 2024, at 4:56 PM, Achilleas Mantzios - cloud 
>  wrote:
> 
> Hello
> 
> I notice both my kids struggling with either C or Python as first programming 
> languages. I believe both are unsuitable for use as introductory languages to 
> college juniors.

https://scratch.mit.edu/ would be more suitable, maybe ? 

> 
> Python IMHO is too advanced, too rich, weird indentation rules, no simple for 
> loop etc.
> 
> C, ok, punishing little kids with segmentation faults, calling by value VS by 
> reference and 3ple pointers is pure sadism.
> 
> So this brings me to memory good old PASCAL from the time I was junior, circa 
> 1986. PL/SQL resembles PASCAL heavily. Everything seems well defined, strong 
> typing, simplicity, I think it has everything a programming language should 
> have in order to be taught as an introductory language. But it lacks IO and 
> file handling.
> 
> So, I ask, have there been any efforts to bring PL/PGSQL to the terminal?

Imho the best you can do is bash + psql + pl/pgsql , via heredocs .
pql  
> Thanks!
> 
> 
> 

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Robert Treat]

On Thu, Mar 7, 2024 at 11:26 AM Pavel Stehule  wrote:
> čt 7. 3. 2024 v 16:59 odesílatel Christophe Pettus  napsal:
>> > On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud 
>> >  wrote:
>> > So, I ask, have there been any efforts to bring PL/PGSQL to the terminal?
>>
>> Strictly speaking, of course, you can use PL/pgSQL from the terminal 
>> already: just use psql, connect to the database, and create and run 
>> functions and procedures as much as you like.
>>
>> If the question is, "Have there been any efforts to implement a PL/pgSQL 
>> interpreter without PostgreSQL?", that's a different and much more complex 
>> problem.  PL/pgSQL uses the PostgreSQL query execution machinery to run 
>> pretty much anything that is not a control structure, and the language is 
>> very focused on interacting with the database.  I doubt it would be worth 
>> anyone's time to try to build some kind of minimal framework that implements 
>> the SPI to allow PL/pgSQL to operate without PostgreSQL.
>
>
> yes
>
> plpgsql cannot exist without Postgres. PL/pgSQL is strongly reduced 
> interpreted Ada language. The gcc compiler supports Ada language.
>
> I found https://bush.sourceforge.net/bushref.html - it is interpret with Ada 
> syntax, but it is better to learn Python - it is easy - with a pretty big 
> library.
>
> free pascal https://www.freepascal.org/ is good compiler and you can write 
> terminal applications too - with Turbo Vision
>

Of course there's a certain amount of personal preference with all
this stuff. I started with basic and really liked it, and then had to
learn pascal and hated it so much that I decided to eschew programming
for years. If you are just trying to learn for fun, I see no reason
why SQL, paired with data in a database, wouldn't be worth spending
time on. Once you're comfortable with that, I like ruby on the command
line and it interacts nicely with databases, and also works well
within the rails console. That said, my son liked lua when he was a
kid, so yeah, there's lots of options, even if plpgsql on the command
line isn't strictly one of them.

Robert Treat
https://xzilla.net

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Achilleas Mantzios]

Στις 7/3/24 17:13, ο/η Karsten Hilbert έγραψε:

Am Thu, Mar 07, 2024 at 04:56:18PM +0200 schrieb Achilleas Mantzios - cloud:


Python IMHO is too advanced, too rich,

Python _is_ powerful but it needn't be difficult.


weird indentation rules

A matter of taste IMO.


no simple for loop

Really ?

Python 3.11.2 (main, Mar 13 2023, 12:18:29) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> for idx in [0,1,2,3]: print(idx)



Dude this is like saying that for n=N the statement itself takes O(N) to 
write, this is not only "not-simple", this is a darn anti-pattern , that 
should be avoided at any cost in 1st year of college.




...
0
1
2
3
>>>

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B



--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Achilleas Mantzios]

Στις 7/3/24 18:25, ο/η Pavel Stehule έγραψε:



čt 7. 3. 2024 v 16:59 odesílatel Christophe Pettus  
napsal:




> On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud
 wrote:
> So, I ask, have there been any efforts to bring PL/PGSQL to the
terminal?

Strictly speaking, of course, you can use PL/pgSQL from the
terminal already: just use psql, connect to the database, and
create and run functions and procedures as much as you like.

If the question is, "Have there been any efforts to implement a
PL/pgSQL interpreter without PostgreSQL?", that's a different and
much more complex problem.  PL/pgSQL uses the PostgreSQL query
execution machinery to run pretty much anything that is not a
control structure, and the language is very focused on interacting
with the database. I doubt it would be worth anyone's time to try
to build some kind of minimal framework that implements the SPI to
allow PL/pgSQL to operate without PostgreSQL.


yes

plpgsql cannot exist without Postgres. PL/pgSQL is strongly reduced 
interpreted Ada language. The gcc compiler supports Ada language.


I found https://bush.sourceforge.net/bushref.html - it is interpret 
with Ada syntax, but it is better to learn Python - it is easy - with 
a pretty big library.

Cool, thank you.


free pascal https://www.freepascal.org/ is good compiler and you can 
write terminal applications too - with Turbo Vision

Too bad, PASCAL is now neglected by most UNIs :(


Regards

Pavel


--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Achilleas Mantzios]

Στις 7/3/24 18:44, ο/η Robert Treat έγραψε:

On Thu, Mar 7, 2024 at 11:26 AM Pavel Stehule  wrote:

čt 7. 3. 2024 v 16:59 odesílatel Christophe Pettus  napsal:

On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud 
 wrote:
So, I ask, have there been any efforts to bring PL/PGSQL to the terminal?

Strictly speaking, of course, you can use PL/pgSQL from the terminal already: 
just use psql, connect to the database, and create and run functions and 
procedures as much as you like.

If the question is, "Have there been any efforts to implement a PL/pgSQL interpreter 
without PostgreSQL?", that's a different and much more complex problem.  PL/pgSQL 
uses the PostgreSQL query execution machinery to run pretty much anything that is not a 
control structure, and the language is very focused on interacting with the database.  I 
doubt it would be worth anyone's time to try to build some kind of minimal framework that 
implements the SPI to allow PL/pgSQL to operate without PostgreSQL.


yes

plpgsql cannot exist without Postgres. PL/pgSQL is strongly reduced interpreted 
Ada language. The gcc compiler supports Ada language.

I found https://bush.sourceforge.net/bushref.html - it is interpret with Ada 
syntax, but it is better to learn Python - it is easy - with a pretty big 
library.

free pascal https://www.freepascal.org/ is good compiler and you can write 
terminal applications too - with Turbo Vision


Of course there's a certain amount of personal preference with all
this stuff. I started with basic and really liked it, and then had to
learn pascal and hated it so much that I decided to eschew programming
for years. If you are just trying to learn for fun, I see no reason
why SQL, paired with data in a database, wouldn't be worth spending
time on. Once you're comfortable with that, I like ruby on the command
line and it interacts nicely with databases, and also works well
within the rails console. That said, my son liked lua when he was a
kid, so yeah, there's lots of options, even if plpgsql on the command


I am not talking for fun. I am talking about the future programmers of 
this world. Teaching Python or C to them upon arrival to the classes 
seems so wrong in every aspect.


What do UNIs in USA or Europe or Asia teach in 1st semester ?


line isn't strictly one of them.

Robert Treat
https://xzilla.net


--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Karsten Hilbert]

Am Thu, Mar 07, 2024 at 08:04:21PM +0200 schrieb Achilleas Mantzios:

> > Python 3.11.2 (main, Mar 13 2023, 12:18:29) [GCC 12.2.0] on linux
> > Type "help", "copyright", "credits" or "license" for more information.
> > >>> for idx in [0,1,2,3]: print(idx)
>
> Dude this is like saying that for n=N the statement itself takes O(N) to write

You do realize this is a conceptual example ...

> that should be avoided at any cost
> in 1st year of college.

.. which I'd fully expect to be teachable to 1st year of
college folks ?

However, let's refrain from discussing that _here_.

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Adrian Klaver]

On 3/7/24 10:13, Achilleas Mantzios wrote:

Στις 7/3/24 18:44, ο/η Robert Treat έγραψε:




I am not talking for fun. I am talking about the future programmers of 
this world. Teaching Python or C to them upon arrival to the classes 
seems so wrong in every aspect.


Seems to me you need to tackle this from the other end, that is what are 
you looking for in a first language?




What do UNIs in USA or Europe or Asia teach in 1st semester ?


line isn't strictly one of them.

Robert Treat
https://xzilla.net




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

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Achilleas Mantzios]

Στις 7/3/24 21:29, ο/η Adrian Klaver έγραψε:

On 3/7/24 10:13, Achilleas Mantzios wrote:

Στις 7/3/24 18:44, ο/η Robert Treat έγραψε:




I am not talking for fun. I am talking about the future programmers 
of this world. Teaching Python or C to them upon arrival to the 
classes seems so wrong in every aspect.


Seems to me you need to tackle this from the other end, that is what 
are you looking for in a first language?


Something like good ol Pascal, just a little more market-correct to make 
it viable. Something that puts sanity and simplicity above impression or 
anything else. C or Python as first languages (like seems the norm among 
UNIs) is suicidal. I am strongly against it. Kids just dont learn the 
essentials. And the path goes like , simple -> lower lever (C/Assembly) 
, but also higher level (C++/Java/Python/etc). Destroying their minds by 
starting with Python or C just minimizes the chances for future great C 
programmers or Java/Python programmers.


Ppl from the community already expressed to me the shortage of new ppl 
willing to write system level (linux/postgersql) C code. And nothing is 
accidental.






What do UNIs in USA or Europe or Asia teach in 1st semester ?


line isn't strictly one of them.

Robert Treat
https://xzilla.net





--
Achilleas Mantzios
 IT DEV - HEAD
 IT DEPT
 Dynacom Tankers Mgmt (as agents only)

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Adrian Klaver]

On 3/7/24 12:20, Achilleas Mantzios wrote:

Στις 7/3/24 21:29, ο/η Adrian Klaver έγραψε:

On 3/7/24 10:13, Achilleas Mantzios wrote:

Στις 7/3/24 18:44, ο/η Robert Treat έγραψε:




I am not talking for fun. I am talking about the future programmers 
of this world. Teaching Python or C to them upon arrival to the 
classes seems so wrong in every aspect.


Seems to me you need to tackle this from the other end, that is what 
are you looking for in a first language?


Something like good ol Pascal, just a little more market-correct to make 
it viable. Something that puts sanity and simplicity above impression or 
anything else. C or Python as first languages (like seems the norm among 
UNIs) is suicidal. I am strongly against it. Kids just dont learn the 
essentials. And the path goes like , simple -> lower lever (C/Assembly) 
, but also higher level (C++/Java/Python/etc). Destroying their minds by 
starting with Python or C just minimizes the chances for future great C 
programmers or Java/Python programmers.


Would that not be covered by a theory of programing course series?

I will admit up front this is getting out of my depth, but from my 
experiences with programming languages they, at a high level, all do the 
same thing basically. Transform text into low level operations on a 
machine. Therefore course work on what those low level operations are 
and the way to abstract above the machine code level would seem to me 
the way to go. Then a series of classes that move languages from lower 
to higher level.




Ppl from the community already expressed to me the shortage of new ppl 
willing to write system level (linux/postgersql) C code. And nothing is 
accidental.






What do UNIs in USA or Europe or Asia teach in 1st semester ?


line isn't strictly one of them.

Robert Treat
https://xzilla.net






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

Re: Is partition pruning impacted by data type

[sud]

Something interesting and not sure if expected behaviour is as below. We
are confused here.

In the below example we created two partitioned tables on timestamptz type
columns with different time zones and the child partitions are created
appropriately with boundaries as one mid night to next mid night and so
on.But when we change the time zone and query the  data dictionary views
again, it shows the start and end of the partition boundary as not
midnights but different values.

So I was wondering if this can cause us any unforeseen issues in the long
run while creating the partitions though partman or while persisting the
data into the tables from the end users? or should we always set the local
timezone as UTC always before running or calling the pg_partman/pg_cron
process which creates the partitions? Mainly in a database which serves
global users sitting across multiple timezones. And same thing while
inserting data into the table, we should use UTC timezone conversion
function.

And while checking the timezone using the "show timezone" function it shows
the local timezone, so is there any way to see postgres DB the server
timezone?

SET SESSION TIME ZONE 'UTC';
CREATE TABLE test_timestamp (
ts TIMESTAMP,
tstz TIMESTAMPTZ) PARTITION BY RANGE (tstz);

SELECT partman.create_parent(
   p_parent_table := 'public.test_timestamp',
   p_control := 'tstz',
   p_type := 'native',
   p_interval := '1 day',
   p_premake := 4,
   p_start_partition => '2024-03-07 00:00:00'
);

UPDATE partman.part_config SET infinite_time_partitions = 'true' WHERE
parent_table = 'public.test_timestamp';

with recursive inh as (
   select i.inhrelid, null::text as parent
   from pg_catalog.pg_inherits i
 join pg_catalog.pg_class cl on i.inhparent = cl.oid
 join pg_catalog.pg_namespace nsp on cl.relnamespace = nsp.oid
   where nsp.nspname = 'public'
 and cl.relname = 'test_timestamp2'
   union all
   select i.inhrelid, (i.inhparent::regclass)::text
   from inh
   join pg_catalog.pg_inherits i on (inh.inhrelid = i.inhparent)
)
select c.relname as partition_name,
pg_get_expr(c.relpartbound, c.oid, true) as partition_expression
from inh
   join pg_catalog.pg_class c on inh.inhrelid = c.oid
   join pg_catalog.pg_namespace n on c.relnamespace = n.oid
   left join pg_partitioned_table p on p.partrelid = c.oid
order by n.nspname, c.relname;

test_timestamp_default DEFAULT
test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-07 00:00:00+00') TO
('2024-03-08 00:00:00+00')
test_timestamp_p2024_03_08 FOR VALUES FROM ('2024-03-08 00:00:00+00') TO
('2024-03-09 00:00:00+00')
test_timestamp_p2024_03_09 FOR VALUES FROM ('2024-03-09 00:00:00+00') TO
('2024-03-10 00:00:00+00')
test_timestamp_p2024_03_10 FOR VALUES FROM ('2024-03-10 00:00:00+00') TO
('2024-03-11 00:00:00+00')
test_timestamp_p2024_03_11 FOR VALUES FROM ('2024-03-11 00:00:00+00') TO
('2024-03-12 00:00:00+00')

SET SESSION TIME ZONE 'EST';

test_timestamp_default DEFAULT
test_timestamp_p2024_03_07 FOR VALUES FROM ('2024-03-06 *19:00:00-05*') TO
('2024-03-07 19:00:00-05')
test_timestamp_p2024_03_08 FOR VALUES FROM ('2024-03-07 *19:00:00-05*') TO
('2024-03-08 19:00:00-05')
test_timestamp_p2024_03_09 FOR VALUES FROM ('2024-03-08 *19:00:00-05*') TO
('2024-03-09 19:00:00-05')
test_timestamp_p2024_03_10 FOR VALUES FROM ('2024-03-09 *19:00:00-05*') TO
('2024-03-10 19:00:00-05')
test_timestamp_p2024_03_11 FOR VALUES FROM ('2024-03-10 *19:00:00-05*') TO
('2024-03-11 19:00:00-05')

***

SET SESSION TIME ZONE 'EST';

CREATE TABLE test_timestamp2 (
ts TIMESTAMP,
tstz TIMESTAMPTZ) PARTITION BY RANGE (tstz);

SELECT partman.create_parent(
   p_parent_table := 'public.test_timestamp2',
   p_control := 'tstz',
   p_type := 'native',
   p_interval := '1 day',
   p_premake := 4,
   p_start_partition => '2024-03-07 00:00:00'
);

UPDATE partman.part_config SET infinite_time_partitions = 'true' WHERE
parent_table = 'public.test_timestamp2';

with recursive inh as (
   select i.inhrelid, null::text as parent
   from pg_catalog.pg_inherits i
 join pg_catalog.pg_class cl on i.inhparent = cl.oid
 join pg_catalog.pg_namespace nsp on cl.relnamespace = nsp.oid
   where nsp.nspname = 'public'
 and cl.relname = 'test_timestamp2'
   union all
   select i.inhrelid, (i.inhparent::regclass)::text
   from inh
   join pg_catalog.pg_inherits i on (inh.inhrelid = i.inhparent)
)
select c.relname as partition_name,
pg_get_expr(c.relpartbound, c.oid, true) as partition_expression
from inh
   join pg_catalog.pg_class c on inh.inhrelid = c.oid
   join pg_catalog.pg_namespace n on c.relnamespace = n.oid
   left join pg_partitioned_table p on p.partrelid = c.oid
order by n.nspname, c.relname;

test_timestamp2_default DEFAULT
test_timestamp2_p2024_03_07 FOR VALUES FROM ('2024-03-07 00:00:00-05') TO
('2024-03-08 00:00:00-05')
test_timestamp2_p2024_03_08 FOR VALUES FROM ('2024-03-08 00:00:00-05') TO
('2024-03-09 00:00:00-05')
test_timestamp2_p2024_03_09 FOR VALUES FROM ('2024-0

Re: Thoughts on user-defined types for talk at Postgres conference?

[Peter J. Holzer]

On 2024-03-06 12:45:33 -0500, Stephen Frost wrote:
> =*# select pg_column_size(t2.*) from t2;
>  pg_column_size 
> 
>  53
> (1 row)
> 
> That's an additional 21 bytes, which is really quite a lot.  What's
> included in those 21 bytes are
[...]
> the type information (typmod if there is one and the OID of the
> composite type),

Is it necessary to store this in every row? Can a column contain
different composite types?

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: Thoughts on user-defined types for talk at Postgres conference?

[Tom Lane]

"Peter J. Holzer"  writes:
> On 2024-03-06 12:45:33 -0500, Stephen Frost wrote:
>> That's an additional 21 bytes, which is really quite a lot.  What's
>> included in those 21 bytes are
>> [...]
>> the type information (typmod if there is one and the OID of the
>> composite type),

> Is it necessary to store this in every row? Can a column contain
> different composite types?

Operations on type RECORD need to be able to ascertain which concrete
rowtype they're dealing with, so yes the type info is critical in
that case.  Even without that, it wouldn't be terribly practical
to insist on creating a separate copy of record_out (and every other
function that accepts composite types) just so that it could have a
hard-wired notion of what rowtype it's going to deal with.

regards, tom lane