Re: Passing a dynamic interval to generate_series()
Hi Igal: On Mon, 1 Jul 2024 at 01:17, Igal Sapir wrote: > I actually did test the expression that I posted, but it might be casting it > twice. While your examples that you wrote show 1 month correctly: > SELECT (interval '1 ' || 'month'); > ?column? | > -+ > 00:00:01month| No, it does not, try it like this: s=> with a(x) as ( SELECT (interval '1 ' || 'month')) select x, pg_typeof(x) from a; x | pg_typeof ---+--- 00:00:01month | text (1 row) And you'll understand what is happening. Cast to interval has higher priority then concatenation, so you are selecting a 1 second interval, casting it to text, '00:00:01', adding 'month' at end. This can also be noticed because month output would not use ':' and have spaces: s=> with a(x) as ( SELECT '001.00MONTHS'::interval) select x, pg_typeof(x) from a; x | pg_typeof ---+--- 1 mon | interval (1 row) ( I used fractions, uppercase and no spaces on input to show how interval output normalizes ). Francisco Olarte.
Re: Passing a dynamic interval to generate_series()
Igal Sapir schrieb am 01.07.2024 um 00:39:
> I am trying to pass a dynamic interval to generate_series() with date range.
>
> This works as expected, and generates a series with an interval of 1 month:
>
> SELECT generate_series(
> date_trunc('month', current_date),
> date_trunc('month', current_date + interval '7 month'),
> interval '1 month'
> )
>
>
> This works as expected and returns an interval of 1 month:
>
> SELECT ('1 ' || 'month')::interval;
>
>
> But this throws an error (SQL Error [42601]: ERROR: syntax error at or near
> "'1 '"):
>
> SELECT generate_series(
> date_trunc('month', current_date),
> date_trunc('month', current_date + interval '7 month'),
> interval ('1 ' || 'month')::interval
> )
I am a fan of make_interval() when it comes to creating intervals from dynamic
parameters:
SELECT generate_series(
date_trunc('month', current_date),
date_trunc('month', current_date + interval '7 month'),
make_interval(months => 1)
)
The value for make_interval() can e.g. passed as a parameter from your
programming language.
MVCC: Using function doing INSERT and returning PK in WHERE clause of SELECT statement
Hi. So for business rule reason, INSERT'ing a new row is wrapped in a DEFINER function, that returns the newly inserted row's OK (and integral ID). And in the code calling that function, I was SELECT * FROM table WHERE ID = insert_row_via_func() to "save a round trip", combining the insertion and the select. But of course, it didn't work, and in hindsight, the SCN for the SELECT is chosen before the INSERT done inside the function, so the new row is not seen, and the select returns nothing. The work-around is easy, just make two statements. But of course that's two round-trips. I looked at DO blocks, to perform both statements in the same rtrip. But DO block don't return anything. I guess I could have two functions, the existing one just returning the ID, and another returning the table row type, and the latter calling the former? That's just more code to maintain. Is my analysis correct? In terms of the problem? And the solutions / work-arounds? Thanks for any insights. --DD
Re: MVCC: Using function doing INSERT and returning PK in WHERE clause of SELECT statement
On Mon, 2024-07-01 at 10:33 +0200, Dominique Devienne wrote: > INSERT'ing a new row is wrapped in a DEFINER function, > that returns the newly inserted row's OK (and integral ID). > > And in the code calling that function, I was > > SELECT * FROM table WHERE ID = insert_row_via_func() > > to "save a round trip", combining the insertion and the select. > But of course, it didn't work, and in hindsight, the SCN for the > SELECT is chosen before the INSERT done inside the function, > so the new row is not seen, and the select returns nothing. > > Is my analysis correct? In terms of the problem? > And the solutions / work-arounds? That looks corrent. Just define the function as RETURNS "table" and use INSERT ... RETURNING * Yours, Laurenz Albe
Question on partman extension while relation exist
Hello All,
In postgres we are seeing issues during automatic partition maintenance
using pg_partman extension. So basically it automatically creates one new
partition and drops one historical partition each day based on the set
retention period in part_config. We just call it like
partman.run_maintenance_proc('table_name');
While there exists foreign key relationships between the partitioned
tables, Mainly during dropping the parent partitions it takes a lot of
time, as it validates every child table partitions record and also is
taking lock longer. Ideally it should check only the respective parent
partition, but it's not doing that because the foreign key is defined in
table level rather than partition level. So we are planning to create the
foreign keys on the partition level but not at table level.
And we were thinking of doing it dynamically by having an "event trigger"
which will fire on "create statement" i.e while the "create new partition"
statement will be triggered by the Pg_partman. It will try to also create
the foreign key constraints on the new child partition referring to the
respective parent partition during the same time. So that things will be
automated.
But now we are stuck in one scenario , say for example if we execute the
pg_partman for the parent table first then it will create the new partition
independently which is fine, but when it will try to drop the historical
partition, it will complain stating the child partition already exists.
On the other hand,
If we run the pg_partman for the child table first, then it will drop the
historical child partition without any issue , however it will throw an
error while creating the foreign key , as because the respective parent
partition has not yet been created.
Need advice, how we should handle this scenario. Basically in which order
we should call the "pg_partman.run_maintenance_proc" for the parent and
child tables?
