date:20220720

You've never shown us *exactly what you did*, along with any *error messages*.

On 7/19/22 22:07, Marc Millas wrote:

Postgres installed, but not postgis.. which is why I need some help...

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 

On Tue, Jul 19, 2022 at 11:43 PM Adrian Klaver  
wrote:

On 7/19/22 2:09 PM, Marc Millas wrote:
> I did run each step of the script and did install a postgres 12.11.
> then destroyed the instance created by the script, and, then
> pg_createcluster a new one, which is running fine.

Does this mean you have PostGIS installed now?

> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com  

-- 
Adrian Klaver

adrian.kla...@aklaver.com

--
Angular momentum makes the world go 'round.

Re: Batch process


On 7/20/22 00:08, Rama Krishnan wrote:

Hi All,

I am doing purge activity my sales table contains 5M records I am going to 
delete more than 1 year data (which was 3M) records so it was running more 
so I want to do batch wise deletion through plsql





 created or replace function data_purge() returns void as$$
Declare
Begin
Drop table test_old;
Create table test_old as select * from sales where bill_date-interval '1 year';


Delete table sales where sales_id in (select sales_id from test_old;

End;
$$ language plpgsql;


Kindly guide me


Why not just DELETE FROM sales WHERE bill_date

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

2022-07-20 Thread Sebastien Flaesch

Thomas, we already have a similar solution.
The idea is to use the native PostgreSQL SERIAL type.
Seb

From: Thomas Kellerer 
Sent: Wednesday, July 20, 2022 8:56 AM
To: pgsql-general@lists.postgresql.org 
Subject: Re: Concurrent INSERT statements with RETURNING clause resetting 
SERIAL sequence

EXTERNAL: Do not click links or open attachments if you do not recognize the 
sender.

Sebastien Flaesch schrieb am 19.07.2022 um 18:50:
> Tom,
>
> /If that's the behavior you want, you can build it out of standard SQL 
> facilities (e.g. update a one-row table).
> /
>
> Can you elaborate please?
>
> Do you mean the code should use an UPDATE on a one-row table to acquire a 
> lock?

I assume something like this:

https://urldefense.com/v3/__https://blog.sql-workbench.eu/post/gapless-sequence/__;!!I_DbfM1H!F7_2cNahve0cmwPMP6QBBwwpyP6UAum4ukFj71_21ebcxTKXZFtU0_3O6l1lfG5jYiKjO7wEzRt_E1GbJ9Q$

operator does not exist: text = bytea

2022-07-20 Thread Karthik K L V

Hi Team,

I am getting the below error while executing a Select query using Spring
DataJPA and Hibernate framework in Aurora Postgres SQL.



*Caused by: org.postgresql.util.PSQLException: ERROR: operator does not
exist: text = bytea  Hint: No operator matches the given name and argument
types. You might need to add explicit type casts.  Position: 1037*

We have a query with bind value which sometimes gets resolved to null (no
data) depending on the application scenario.
The datatype of the bindvalue and the corresponding column is String.
The same query executes fine when the value of the bindvalue is populated.

Could you please let me know how I can resolve this issue without making
changes to the query?
Is there any configuration available in the Postgres Driver or on the
Postgres DB Server which can be set to resolve null bind values?

Appreciate your help. Thank you.

-- 
Karthik klv

Re: Concurrent INSERT statements with RETURNING clause resetting SERIAL sequence

2022-07-20 Thread Karsten Hilbert

Am Wed, Jul 20, 2022 at 09:15:29AM + schrieb Sebastien Flaesch:

> Thomas, we already have a similar solution.
> The idea is to use the native PostgreSQL SERIAL type.

Which does not guarantuee gaplessness.

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

Re: operator does not exist: text = bytea

2022-07-20 Thread Karthik K L V

Update:

Followed this thread
PostgreSQL: Re: Null bind variable in where clause


and set *transform_null_equals to ON* in the parameter group on the AWS
Console. But no luck.

We are using Aurora PostgresSQL v13.3

On Wed, Jul 20, 2022 at 3:02 PM Karthik K L V 
wrote:

> Hi Team,
>
> I am getting the below error while executing a Select query using Spring
> DataJPA and Hibernate framework in Aurora Postgres SQL.
>
>
>
> *Caused by: org.postgresql.util.PSQLException: ERROR: operator does not
> exist: text = bytea  Hint: No operator matches the given name and argument
> types. You might need to add explicit type casts.  Position: 1037*
>
> We have a query with bind value which sometimes gets resolved to null (no
> data) depending on the application scenario.
> The datatype of the bindvalue and the corresponding column is String.
> The same query executes fine when the value of the bindvalue is populated.
>
> Could you please let me know how I can resolve this issue without making
> changes to the query?
> Is there any configuration available in the Postgres Driver or on the
> Postgres DB Server which can be set to resolve null bind values?
>
> Appreciate your help. Thank you.
>
> --
> Karthik klv
>


-- 
Karthik klv

Patroni & PostgreSQL issue

2022-07-20 Thread Sankar, Uma (Uma)

Hi All,

This is regarding the Postgres HA working with patroni in 3 node setup, we have 
an issue with the primary because a few database files were deleted manually so 
performed a switch over to move the services from primary to secondary with 
patroni, post the switchover was deleted file was re-created by itself in the 
old primary.

Now we are planning to switch back the services from secondary(Leader) to 
primary(replica) as the deleted files were re-created itself, can someone 
please suggest if this works normally when we switch back to promote primary as 
the leader and secondary as a replica.


Regards,
Uma Sankar

Re: operator does not exist: text = bytea

2022-07-20 Thread hubert depesz lubaczewski

On Wed, Jul 20, 2022 at 03:02:13PM +0530, Karthik K L V wrote:
> *Caused by: org.postgresql.util.PSQLException: ERROR: operator does not
> exist: text = bytea  Hint: No operator matches the given name and argument
> types. You might need to add explicit type casts.  Position: 1037*
> Could you please let me know how I can resolve this issue without making
> changes to the query?

I don't think it's possible.

bytea is basically array of bytes.
text on the other hand is array of characters.

Do the bytes "\xbf\xf3\xb3\x77" equal text "żółw"?

They actually kinda do, if we assume the bytes are text in encoding
Windows-1252 - in which case the bytes mean "żółw".

But if we'd assume the encoding to be, for example, iso8859-1, then the
same sequence of bytes means "¿ó³w"

That's why you can't compare bytes to characters.

You have to either convert bytes to text using convert or convert_from
functions, or change text into bytea using convert_to.

In some cases you can simply cast text to bytea:

$ select 'depesz'::text::bytea;
 bytea  

 \x64657065737a
(1 row)

which will work using current server encoding, afair.

depesz

Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity

2022-07-20 Thread Achilleas Mantzios

On 19/7/22 20:31, David G. Johnston wrote:

On Tuesday, July 19, 2022, Achilleas Mantzios
wrote:

Thanks David

Στις 19/7/22 17:19, ο/η David G. Johnston έγραψε:

On Tuesday, July 19, 2022, Achilleas Mantzios
wrote:

ERROR: cannot convert infinity to numeric

-- has no problem testing against infinity

select it.id ,cept.value::numeric as val,
numrange(ceptl.min_alarm::numeric,ceptl.max_alarm::numeric
,'()') as range from items it, cept_report cept , dynacom.vessels vsl,
machdefs md, cept_reportlimits ceptl wh
ere it.id =cept.id AND md.defid=ceptl.defid
AND it.defid=md.defid AND it.vslwhid=vsl.id AND vsl.vslstatus='Acti
ve' and md.application = 'Critical Equipment Performance Test' AND
cept.systemdate>= (now()-'1 year'::interval
) AND cept.value='inf' ORDER BY 1;
id | val | range
+-+---
(0 rows)

The column cept.value contains an infinity. I see nothing unusual in any
of these queries given that fact. If you try to cast the infinity to numeric it
will fail. If that doesn’t happen the
query won’t fail.

Sorry I must have been dizzy today with so much support.

Yep, there are some infinity in there, but not in this result set.

I think when the casting is in the WHERE filter for some reason some
subplan uses this filter (and fails). But when this check is applied to the
result, no infinity is found and works correctly.

That is what it means for SQL to be a declarative language, the order of execution/evaluation is determined to be efficient and not what is explicitly written. You do have some control though, but
using it also means you might make things worse.

I think you have issues anyway if you are doing equality checks on what seems
to be a floating point column, regardless of which way you do the cast.

Thank you, will look into it further when I get the time.

David J.

--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Re: pgsql 10.19 : "ERROR: cannot convert infinity to numeric" except there is no infinity

2022-07-20 Thread Achilleas Mantzios


On 19/7/22 20:32, Adrian Klaver wrote:

On 7/19/22 10:26 AM, Achilleas Mantzios wrote:

Thank you Adrian!


Actually thank:

https://sqlformat.darold.net/



Στις 19/7/22 18:36, ο/η Adrian Klaver έγραψε:

On 7/19/22 03:38, Achilleas Mantzios wrote:

I reformatted queries to see thing better.






AND numrange(ceptl.min_alarm::numeric, ceptl.max_alarm::numeric, '()') @> 
cept.value::numeric
ORDER BY
    1;

So the above fails. In your title when you say there is no infinity that means 
the cept.value, ceptl.min_alarm or ceptl.max_alarm  fields do not have any 
'-infinity' or 'infinity' values, correct?

There is infinity in cept.value , just not in this result set.
I got confused and wrongly assumed that since the result set (without the filter in the WHERE clause including cept.value::numeric) did not contain any infinity it should also work with the filter 
in the WHERE clause. Apparently a subplan executes this conversion in the WHERE before the other filters. I did not do any analyze to prove this.




Have you tried:

NULLIF(cept.value, 'inf')::numeric

no, cause the CTE version worked. Will keep in mind for similar future problems.



--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt













--
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt

Re: operator does not exist: text = bytea

2022-07-20 Thread Karthik K L V

Hi  depesz,

Thanks for your reply. But, this issue is happening only when the bind
value of the query resolves to null. I am not trying to compare text to
bytes.
And the same query works fine when the bind value gets resolves to some
String. So, looking for an option which can tell Postgres Engine to read *=
null* as *is null*.

On Wed, Jul 20, 2022 at 5:29 PM hubert depesz lubaczewski 
wrote:

> On Wed, Jul 20, 2022 at 03:02:13PM +0530, Karthik K L V wrote:
> > *Caused by: org.postgresql.util.PSQLException: ERROR: operator does not
> > exist: text = bytea  Hint: No operator matches the given name and
> argument
> > types. You might need to add explicit type casts.  Position: 1037*
> > Could you please let me know how I can resolve this issue without making
> > changes to the query?
>
> I don't think it's possible.
>
> bytea is basically array of bytes.
> text on the other hand is array of characters.
>
> Do the bytes "\xbf\xf3\xb3\x77" equal text "żółw"?
>
> They actually kinda do, if we assume the bytes are text in encoding
> Windows-1252 - in which case the bytes mean "żółw".
>
> But if we'd assume the encoding to be, for example, iso8859-1, then the
> same sequence of bytes means "¿ó³w"
>
> That's why you can't compare bytes to characters.
>
> You have to either convert bytes to text using convert or convert_from
> functions, or change text into bytea using convert_to.
>
> In some cases you can simply cast text to bytea:
>
> $ select 'depesz'::text::bytea;
>  bytea
> 
>  \x64657065737a
> (1 row)
>
> which will work using current server encoding, afair.
>
> depesz
>


-- 
Karthik klv

Paging through table one row at a ttime

2022-07-20 Thread H

I am running postgres 13 under CentOS 7. I have a need to be able to page 
through a table one row at a time, possibly using pspg (or other tool) allowing 
me to move in either direction in the table one single row at a time. Ideally 
only the columns in the selected row should be visible, even better if there 
would be one column of that row per row in the terminal window but that would 
probably be wishing for too much.

Is there any configuration of pspg that would allow me to do the above?

Re: postgis

???

I did describe precisely what I did:

On 7/19/22 2:09 PM, Marc Millas wrote:
> > I did run each step of the script and did install a postgres 12.11.
> > then destroyed the instance created by the script, and, then
> > pg_createcluster a new one, which is running fine.

no error messages.. Postgres is fine.

My question i(from the beginning) s about the availability of a *postgis *3.0.x
distro for postgres 12 on debian 11.
and, if yes, the name of the package, and the @ of the repo.

To my understanding, such a *postgis* distro does not exist in the
postgresql.org repos
so through this mail list, I was trying to know if it may exist somewhere.

I know that I am supposed to post such a question on the postgis mail list.
But.. I register thru the postgis web site, and didnt get any answer.
...

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Wed, Jul 20, 2022 at 10:25 AM Ron  wrote:

> You've never shown us *exactly what you did*, along with any *error
> messages*.
>
> On 7/19/22 22:07, Marc Millas wrote:
>
> Postgres installed, but not postgis.. which is why I need some help...
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>
>
> On Tue, Jul 19, 2022 at 11:43 PM Adrian Klaver 
> wrote:
>
>> On 7/19/22 2:09 PM, Marc Millas wrote:
>> > I did run each step of the script and did install a postgres 12.11.
>> > then destroyed the instance created by the script, and, then
>> > pg_createcluster a new one, which is running fine.
>>
>> Does this mean you have PostGIS installed now?
>>
>> > Marc MILLAS
>> > Senior Architect
>> > +33607850334
>> > www.mokadb.com 
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
> --
> Angular momentum makes the world go 'round.
>

Re: postgis

This long drama is about *POSTGIS*, not Postgresql.  What error do you get 
when trying to install *POSTGIS*?

On 7/20/22 08:26, Marc Millas wrote:

???

I did describe precisely what I did:

On 7/19/22 2:09 PM, Marc Millas wrote:
> I did run each step of the script and did install a postgres 12.11.
> then destroyed the instance created by the script, and, then
> pg_createcluster a new one, which is running fine.

no error messages.. Postgres is fine.

My question i(from the beginning) s about the availability of a *postgis 
*3.0.x distro for postgres 12 on debian 11.

and, if yes, the name of the package, and the @ of the repo.

To my understanding, such a *postgis* distro does not exist in the 
postgresql.org  repos

so through this mail list, I was trying to know if it may exist somewhere.

I know that I am supposed to post such a question on the postgis mail list.
But.. I register thru the postgis web site, and didnt get any answer.
...

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 

On Wed, Jul 20, 2022 at 10:25 AM Ron  wrote:

You've never shown us *exactly what you did*, along with any *error
messages*.

On 7/19/22 22:07, Marc Millas wrote:

Postgres installed, but not postgis.. which is why I need some help...

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 

On Tue, Jul 19, 2022 at 11:43 PM Adrian Klaver
 wrote:

On 7/19/22 2:09 PM, Marc Millas wrote:
> I did run each step of the script and did install a postgres 12.11.
> then destroyed the instance created by the script, and, then
> pg_createcluster a new one, which is running fine.

Does this mean you have PostGIS installed now?

> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com  

-- 
Adrian Klaver

adrian.kla...@aklaver.com

-- 
Angular momentum makes the world go 'round.

--
Angular momentum makes the world go 'round.

Re: operator does not exist: text = bytea

2022-07-20 Thread Tom Lane

Karthik K L V  writes:
> We have a query with bind value which sometimes gets resolved to null (no
> data) depending on the application scenario.
> The datatype of the bindvalue and the corresponding column is String.
> The same query executes fine when the value of the bindvalue is populated.
> Could you please let me know how I can resolve this issue without making
> changes to the query?

This seems like it depends on the details of how the JDBC driver sends
the bound parameter to the server, so you'd be better off asking in
the pgsql-jdbc mailing list (and supplying some code details).

It's entirely possible that there isn't any solution other than fixing
your app to be more consistent about how it binds the parameter.

regards, tom lane

Re: postgis

???
I didnt get any error, as I dont know the name of the package to be
installed !!!

my question was, and still is:
"Hi,
I would like to install postgis 3.04 on a debian 11.

digging into various web sites, I didnt found the name of that packet.

can someone help ?"

so..
the only info on the debian postgis page I was able to find is the name of
a package:
postgres-12-postgis-3 which do install a postgis 3.2.1
not a postgis 3.0.x






Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Wed, Jul 20, 2022 at 3:52 PM Ron  wrote:

> This long drama is about *POSTGIS*, not Postgresql.  What error do you
> get when trying to install *POSTGIS*?
>
> On 7/20/22 08:26, Marc Millas wrote:
>
> ???
>
> I did describe precisely what I did:
>
> On 7/19/22 2:09 PM, Marc Millas wrote:
>> > I did run each step of the script and did install a postgres 12.11.
>> > then destroyed the instance created by the script, and, then
>> > pg_createcluster a new one, which is running fine.
>
> no error messages.. Postgres is fine.
>
> My question i(from the beginning) s about the availability of a *postgis 
> *3.0.x
> distro for postgres 12 on debian 11.
> and, if yes, the name of the package, and the @ of the repo.
>
> To my understanding, such a *postgis* distro does not exist in the
> postgresql.org repos
> so through this mail list, I was trying to know if it may exist somewhere.
>
> I know that I am supposed to post such a question on the postgis mail list.
> But.. I register thru the postgis web site, and didnt get any answer.
> ...
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>
>
> On Wed, Jul 20, 2022 at 10:25 AM Ron  wrote:
>
>> You've never shown us *exactly what you did*, along with any *error
>> messages*.
>>
>> On 7/19/22 22:07, Marc Millas wrote:
>>
>> Postgres installed, but not postgis.. which is why I need some help...
>>
>> Marc MILLAS
>> Senior Architect
>> +33607850334
>> www.mokadb.com
>>
>>
>>
>> On Tue, Jul 19, 2022 at 11:43 PM Adrian Klaver 
>> wrote:
>>
>>> On 7/19/22 2:09 PM, Marc Millas wrote:
>>> > I did run each step of the script and did install a postgres 12.11.
>>> > then destroyed the instance created by the script, and, then
>>> > pg_createcluster a new one, which is running fine.
>>>
>>> Does this mean you have PostGIS installed now?
>>>
>>> > Marc MILLAS
>>> > Senior Architect
>>> > +33607850334
>>> > www.mokadb.com 
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>
> --
> Angular momentum makes the world go 'round.
>

Re: postgis

2022-07-20 Thread jian he

Can you try compiling from source: https://postgis.net/source/

postgis 3.2.1 is OK. postgresql & postgis version info:
https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS

if you already installed check postgis version:
https://postgis.net/docs/PostGIS_Version.html

On Wed, Jul 20, 2022 at 7:37 PM Marc Millas  wrote:

> ???
> I didnt get any error, as I dont know the name of the package to be
> installed !!!
>
> my question was, and still is:
> "Hi,
> I would like to install postgis 3.04 on a debian 11.
>
> digging into various web sites, I didnt found the name of that packet.
>
> can someone help ?"
>
> so..
> the only info on the debian postgis page I was able to find is the name of
> a package:
> postgres-12-postgis-3 which do install a postgis 3.2.1
> not a postgis 3.0.x
>
>
>
>
>
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>
>
> On Wed, Jul 20, 2022 at 3:52 PM Ron  wrote:
>
>> This long drama is about *POSTGIS*, not Postgresql.  What error do you
>> get when trying to install *POSTGIS*?
>>
>> On 7/20/22 08:26, Marc Millas wrote:
>>
>> ???
>>
>> I did describe precisely what I did:
>>
>> On 7/19/22 2:09 PM, Marc Millas wrote:
>>> > I did run each step of the script and did install a postgres 12.11.
>>> > then destroyed the instance created by the script, and, then
>>> > pg_createcluster a new one, which is running fine.
>>
>> no error messages.. Postgres is fine.
>>
>> My question i(from the beginning) s about the availability of a *postgis
>> *3.0.x distro for postgres 12 on debian 11.
>> and, if yes, the name of the package, and the @ of the repo.
>>
>> To my understanding, such a *postgis* distro does not exist in the
>> postgresql.org repos
>> so through this mail list, I was trying to know if it may exist somewhere.
>>
>> I know that I am supposed to post such a question on the postgis mail
>> list.
>> But.. I register thru the postgis web site, and didnt get any answer.
>> ...
>>
>> Marc MILLAS
>> Senior Architect
>> +33607850334
>> www.mokadb.com
>>
>>
>>
>> On Wed, Jul 20, 2022 at 10:25 AM Ron  wrote:
>>
>>> You've never shown us *exactly what you did*, along with any *error
>>> messages*.
>>>
>>> On 7/19/22 22:07, Marc Millas wrote:
>>>
>>> Postgres installed, but not postgis.. which is why I need some help...
>>>
>>> Marc MILLAS
>>> Senior Architect
>>> +33607850334
>>> www.mokadb.com
>>>
>>>
>>>
>>> On Tue, Jul 19, 2022 at 11:43 PM Adrian Klaver <
>>> adrian.kla...@aklaver.com> wrote:
>>>
 On 7/19/22 2:09 PM, Marc Millas wrote:
 > I did run each step of the script and did install a postgres 12.11.
 > then destroyed the instance created by the script, and, then
 > pg_createcluster a new one, which is running fine.

 Does this mean you have PostGIS installed now?

 > Marc MILLAS
 > Senior Architect
 > +33607850334
 > www.mokadb.com 


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

>>>
>>> --
>>> Angular momentum makes the world go 'round.
>>>
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>

-- 
 I recommend David Deutsch's <>

  Jian

Re: postgis

Thanks for your answer.
I would like to avoid compiling as much as possible.
I know that postgis 3.2.1 is available and does install without pb. but..
That db run an app which is very long to test, so I need to stick to a
postgis 3.0.x

regards,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Wed, Jul 20, 2022 at 4:16 PM jian he  wrote:

>
> Can you try compiling from source: https://postgis.net/source/
>
> postgis 3.2.1 is OK. postgresql & postgis version info:
> https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS
>
> if you already installed check postgis version:
> https://postgis.net/docs/PostGIS_Version.html
>
> On Wed, Jul 20, 2022 at 7:37 PM Marc Millas 
> wrote:
>
>> ???
>> I didnt get any error, as I dont know the name of the package to be
>> installed !!!
>>
>> my question was, and still is:
>> "Hi,
>> I would like to install postgis 3.04 on a debian 11.
>>
>> digging into various web sites, I didnt found the name of that packet.
>>
>> can someone help ?"
>>
>> so..
>> the only info on the debian postgis page I was able to find is the name
>> of a package:
>> postgres-12-postgis-3 which do install a postgis 3.2.1
>> not a postgis 3.0.x
>>
>>
>>
>>
>>
>>
>> Marc MILLAS
>> Senior Architect
>> +33607850334
>> www.mokadb.com
>>
>>
>>
>> On Wed, Jul 20, 2022 at 3:52 PM Ron  wrote:
>>
>>> This long drama is about *POSTGIS*, not Postgresql.  What error do you
>>> get when trying to install *POSTGIS*?
>>>
>>> On 7/20/22 08:26, Marc Millas wrote:
>>>
>>> ???
>>>
>>> I did describe precisely what I did:
>>>
>>> On 7/19/22 2:09 PM, Marc Millas wrote:
 > I did run each step of the script and did install a postgres 12.11.
 > then destroyed the instance created by the script, and, then
 > pg_createcluster a new one, which is running fine.
>>>
>>> no error messages.. Postgres is fine.
>>>
>>> My question i(from the beginning) s about the availability of a *postgis
>>> *3.0.x distro for postgres 12 on debian 11.
>>> and, if yes, the name of the package, and the @ of the repo.
>>>
>>> To my understanding, such a *postgis* distro does not exist in the
>>> postgresql.org repos
>>> so through this mail list, I was trying to know if it may exist
>>> somewhere.
>>>
>>> I know that I am supposed to post such a question on the postgis mail
>>> list.
>>> But.. I register thru the postgis web site, and didnt get any answer.
>>> ...
>>>
>>> Marc MILLAS
>>> Senior Architect
>>> +33607850334
>>> www.mokadb.com
>>>
>>>
>>>
>>> On Wed, Jul 20, 2022 at 10:25 AM Ron  wrote:
>>>
 You've never shown us *exactly what you did*, along with any *error
 messages*.

 On 7/19/22 22:07, Marc Millas wrote:

 Postgres installed, but not postgis.. which is why I need some help...

 Marc MILLAS
 Senior Architect
 +33607850334
 www.mokadb.com



 On Tue, Jul 19, 2022 at 11:43 PM Adrian Klaver <
 adrian.kla...@aklaver.com> wrote:

> On 7/19/22 2:09 PM, Marc Millas wrote:
> > I did run each step of the script and did install a postgres 12.11.
> > then destroyed the instance created by the script, and, then
> > pg_createcluster a new one, which is running fine.
>
> Does this mean you have PostGIS installed now?
>
> > Marc MILLAS
> > Senior Architect
> > +33607850334
> > www.mokadb.com 
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

 --
 Angular momentum makes the world go 'round.

>>>
>>> --
>>> Angular momentum makes the world go 'round.
>>>
>>
>
> --
>  I recommend David Deutsch's <>
>
>   Jian
>
>
>

Re: Batch process


On 7/20/22 01:28, Ron wrote:

On 7/20/22 00:08, Rama Krishnan wrote:

Hi All,

I am doing purge activity my sales table contains 5M records I am 
going to delete more than 1 year data (which was 3M) records so it was 
running more so I want to do batch wise deletion through plsql





 created or replace function data_purge() returns void as$$
Declare
Begin
Drop table test_old;
Create table test_old as select * from sales where bill_date-interval '1 year';


Delete table sales where sales_id in (select sales_id from test_old;

End;
$$ language plpgsql;


Kindly guide me


Why not just DELETE FROM sales WHERE bill_date

Because it looks like the OP is saving the old records in test_old.


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

Re: postgis

On Wednesday, July 20, 2022, Marc Millas  wrote:

> Thanks for your answer.
> I would like to avoid compiling as much as possible.
> I know that postgis 3.2.1 is available and does install without pb. but..
> That db run an app which is very long to test, so I need to stick to a
> postgis 3.0.x
>

I’m not particularly fluent here but working from the PostGIS project
homepage and into the linked package archives I do not see where what you
want exists.  It doesn’t seem like the 3.0.x series is being kept
up-to-date in packaged form.

But you may want to use PostGIS support channels to have a better chance at
getting confirmation/help rather than here.

My general impression is that the packaging, at least for Debian, doesn’t
actually understand how the PostGIS project handles versioning support.
But i may be missing something as I’m doing this web-only and
apt.postgresql.org is basically impossible to inspect on the web.

David J.

Re: Batch process


On 7/19/22 22:08, Rama Krishnan wrote:

Hi All,

I am doing purge activity my sales table contains 5M records I am going 
to delete more than 1 year data (which was 3M) records so it was running 
more so I want to do batch wise deletion through plsql





  created or replace function data_purge() returns void as$$
Declare
Begin
Drop table test_old;
Create table test_old as select * from sales where bill_date-interval '1 year';


Delete table sales where sales_id in (select sales_id from test_old;

End;
$$ language plpgsql;


Kindly guide me



This looks like a case, going forward for partitioning:

https://www.postgresql.org/docs/14/ddl-partitioning.html


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

Re: Batch process

2022-07-20 Thread Rama Krishnan

Hi Adrian,

Thanks for the update.


Is it possible to achieve 1 records deletion on every iteration because
my original table contains 5M records during the deletion process it
consumes more cpu and resources.

On Wed, 20 Jul, 2022, 20:37 Adrian Klaver, 
wrote:

> On 7/19/22 22:08, Rama Krishnan wrote:
> > Hi All,
> >
> > I am doing purge activity my sales table contains 5M records I am going
> > to delete more than 1 year data (which was 3M) records so it was running
> > more so I want to do batch wise deletion through plsql
> >
> >
> >
> >
> >   created or replace function data_purge() returns void as$$
> > Declare
> > Begin
> > Drop table test_old;
> > Create table test_old as select * from sales where bill_date > -interval '1 year';
> >
> > Delete table sales where sales_id in (select sales_id from test_old;
> >
> > End;
> > $$ language plpgsql;
> >
> >
> > Kindly guide me
> >
>
> This looks like a case, going forward for partitioning:
>
> https://www.postgresql.org/docs/14/ddl-partitioning.html
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Batch process

2022-07-20 Thread Rob Sargent


On 7/20/22 09:26, Rama Krishnan wrote:

Hi Adrian,

Thanks for the update.


Is it possible to achieve 1 records deletion on every iteration 
because my original table contains 5M records during the deletion 
process it consumes more cpu and resources.



See here 
 for 
date manipulation functions.  you can delete by month or week, which 
ever makes it fit your hardware

Re: postgis


On 7/20/22 07:57, David G. Johnston wrote:
On Wednesday, July 20, 2022, Marc Millas > wrote:


My general impression is that the packaging, at least for Debian, 
doesn’t actually understand how the PostGIS project handles versioning 
support.  But i may be missing something as I’m doing this web-only and 
apt.postgresql.org  is basically impossible 
to inspect on the web.


Are you looking for?:

https://apt.postgresql.org/pub/repos/apt/



David J.




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

Re: postgis

On Wed, Jul 20, 2022 at 8:36 AM Adrian Klaver 
wrote:

> On 7/20/22 07:57, David G. Johnston wrote:
> > On Wednesday, July 20, 2022, Marc Millas  > > wrote:
>
> > My general impression is that the packaging, at least for Debian,
> > doesn’t actually understand how the PostGIS project handles versioning
> > support.  But i may be missing something as I’m doing this web-only and
> > apt.postgresql.org  is basically impossible
> > to inspect on the web.
>
> Are you looking for?:
>
> https://apt.postgresql.org/pub/repos/apt/
>
>
That is what I found, and nowhere on the web can I find confirmation of the
existence of the package:

3.0.3+dfsg-2.pgdg+1

Even though I know it exists from this message to the mailing list:

https://www.postgresql.org/message-id/E1khVKm-0005cE-Kc%40atalia.postgresql.org

(and the fact that it is that package that got bundled into the official
docker image)

It seems to most likely be knowledge that is buried in a gz file somewhere
which doesn't do me any good, that I can tell, when viewing the site in
Chrome.
David J.

Re: postgis


On 7/20/22 07:21, Marc Millas wrote:

Thanks for your answer.
I would like to avoid compiling as much as possible.
I know that postgis 3.2.1 is available and does install without pb. but..
That db run an app which is very long to test, so I need to stick to a 
postgis 3.0.x


Alright as close as could get. The procedure is:

1) Go to Postgres APT archive:

https://apt-archive.postgresql.org/

Add to you sources:

deb https://apt-archive.postgresql.org/pub/repos/apt 
bullseye-pgdg-archive main


Then:

sudo apt update

This will get you older versions of packages

2) Find package versions( I was doing this on Ubuntu 20.04 so the distro 
will be different from what you see):


apt list -a postgresql-12-postgis-3
Listing... Done
postgresql-12-postgis-3/focal-pgdg,focal-pgdg-archive 
3.2.1+dfsg-1.pgdg20.04+1 amd64

postgresql-12-postgis-3/focal-pgdg-archive 3.2.0+dfsg-1.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal-pgdg-archive 3.1.4+dfsg-3.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal-pgdg-archive 3.1.4+dfsg-1.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal-pgdg-archive 3.1.3+dfsg-1~exp1.pgdg20.04+1 
amd64
postgresql-12-postgis-3/focal-pgdg-archive 3.1.2+dfsg-1~exp2.pgdg20.04+1 
amd64

postgresql-12-postgis-3/focal-pgdg-archive 3.1.1+dfsg-1.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal-pgdg-archive 3.1.0+dfsg-1.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal-pgdg-archive 
3.1.0~alpha1+dfsg-1~exp2~232.gitc62f703.pgdg20.04+1 amd64

postgresql-12-postgis-3/focal-pgdg-archive 3.0.3+dfsg-2.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal-pgdg-archive 3.0.2+dfsg-4.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal-pgdg-archive 3.0.2+dfsg-2.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal-pgdg-archive 3.0.1+dfsg-4.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal-pgdg-archive 3.0.1+dfsg-2.pgdg20.04+1 amd64
postgresql-12-postgis-3/focal 3.0.0+dfsg-6ubuntu4 amd64

3) From above the latest 3.0.x is 3.0.3. So then do:

sudo apt install  postgresql-12-postgis-3=3.0.3+dfsg-2.pgdg20.04+1


On my machine it installed PostGIS 3.0.3 with no issues.



regards,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 



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

Re: postgis


On 7/20/22 08:55, David G. Johnston wrote:

That is what I found, and nowhere on the web can I find confirmation of 
the existence of the package:


3.0.3+dfsg-2.pgdg+1


https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgis/

See my most recent reply to Marc on how to get at it.



Even though I know it exists from this message to the mailing list:

https://www.postgresql.org/message-id/E1khVKm-0005cE-Kc%40atalia.postgresql.org 



(and the fact that it is that package that got bundled into the official 
docker image)


It seems to most likely be knowledge that is buried in a gz file 
somewhere which doesn't do me any good, that I can tell, when viewing 
the site in Chrome.

David J.




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

Re: postgis

2022-07-20 Thread Imre Samu

> My general impression is that the packaging, at least for Debian,
> doesn’t actually understand how the PostGIS project handles versioning
support.
> But i may be missing something

"PostGIS Pre-built Binary Distributions for various OS"
--->  https://trac.osgeo.org/postgis/wiki/UsersWikiPackages

Debian is a conservative Linux.

IMHO:
Packaging is not so easy, [
https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS  ]
- there are [n.=7] Postgres version [9.6,10,11,12,13,14,15 ]   [ now: all
supported in bullseye ]
- there are [g.=9 ] Geos version [3.3,3.4,3.5,3.6,3.7,3.8,3.9,3.10,3.11]  [
now: bullsey= 3.9.0 ]
- there are [p.=7 ] Proj version [ 4.8,4.9,5.x,6.x,7.x,8.x,9.x ][ now:
bullseye = 7.2.1 ]
- there are [d.= 7 ] Gdal version [ 2.4,3.0,3.1,3.2,3.3,3.4,3.5][ now:
bullseye = 3.2.2 ]
- there are [m.=5] Postgis version [2.4,2.5,3.0,3.1,3.2,3.3]   [now:
bullseye= 3.2.1 ]

And there are also projects based on PostGIS.
- Pgrouting [r.=7 ]   [2.3,2.4,2.5,2.6,3.0,3.1,3.2,3.3]  [ now:
bullseye= 3.3.0 ; postgresql-12-pgrouting ]

So the ideal "end user" combination =  n*g*p*d*m*r  = 7*9*7*7*5*7  = 108045

// disclaimer:   I am a Postgis user and a
https://github.com/postgis/docker-postgis contributor

Regards,
 Imre


David G. Johnston  ezt írta (időpont: 2022.
júl. 20., Sze, 16:58):

> On Wednesday, July 20, 2022, Marc Millas  wrote:
>
>> Thanks for your answer.
>> I would like to avoid compiling as much as possible.
>> I know that postgis 3.2.1 is available and does install without pb. but..
>> That db run an app which is very long to test, so I need to stick to a
>> postgis 3.0.x
>>
>
> I’m not particularly fluent here but working from the PostGIS project
> homepage and into the linked package archives I do not see where what you
> want exists.  It doesn’t seem like the 3.0.x series is being kept
> up-to-date in packaged form.
>
> But you may want to use PostGIS support channels to have a better chance
> at getting confirmation/help rather than here.
>
> My general impression is that the packaging, at least for Debian, doesn’t
> actually understand how the PostGIS project handles versioning support.
> But i may be missing something as I’m doing this web-only and
> apt.postgresql.org is basically impossible to inspect on the web.
>
> David J.
>
>

Re: postgis

On Wed, Jul 20, 2022 at 9:16 AM Adrian Klaver 
wrote:

> On 7/20/22 08:55, David G. Johnston wrote:
>
> > That is what I found, and nowhere on the web can I find confirmation of
> > the existence of the package:
> >
> > 3.0.3+dfsg-2.pgdg+1
>
> https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgis/
>
> See my most recent reply to Marc on how to get at it.
>
>
Right, I vaguely knew how to get at it while at the Linux terminal, wasn't
my point.

That does confirm that two releases are for sure missing and I'm doubting
the 3.0.6 release from yesterday will ever be published as well.

The fact that the package is "-postgis-3" and not "-postgis-3.0" is why I
say the packaging doesn't understand the project's versioning system.

David J.

Re: postgis

On Wed, Jul 20, 2022 at 9:21 AM Imre Samu  wrote:

> > My general impression is that the packaging, at least for Debian,
> > doesn’t actually understand how the PostGIS project handles versioning
> support.
> > But i may be missing something
>
> "PostGIS Pre-built Binary Distributions for various OS"
> --->  https://trac.osgeo.org/postgis/wiki/UsersWikiPackages
>
> Debian is a conservative Linux.
>
> IMHO:
> Packaging is not so easy, [
> https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS  ]
> - there are [n.=7] Postgres version [9.6,10,11,12,13,14,15 ]   [ now: all
> supported in bullseye ]
> - there are [g.=9 ] Geos version [3.3,3.4,3.5,3.6,3.7,3.8,3.9,3.10,3.11]
> [ now: bullsey= 3.9.0 ]
> - there are [p.=7 ] Proj version [ 4.8,4.9,5.x,6.x,7.x,8.x,9.x ][ now:
> bullseye = 7.2.1 ]
> - there are [d.= 7 ] Gdal version [ 2.4,3.0,3.1,3.2,3.3,3.4,3.5][ now:
> bullseye = 3.2.2 ]
> - there are [m.=5] Postgis version [2.4,2.5,3.0,3.1,3.2,3.3]   [now:
> bullseye= 3.2.1 ]
>
> And there are also projects based on PostGIS.
> - Pgrouting [r.=7 ]   [2.3,2.4,2.5,2.6,3.0,3.1,3.2,3.3]  [ now:
> bullseye= 3.3.0 ; postgresql-12-pgrouting ]
>
> So the ideal "end user" combination =  n*g*p*d*m*r  = 7*9*7*7*5*7  = 108045
>
> // disclaimer:   I am a Postgis user and a
> https://github.com/postgis/docker-postgis contributor
>
>>
>>
Yes, my expectation may be naive, but as the package name is
"postgresql-[version]-postgis-[version]" I would expect the 35 packages
implied by the version policies of those two projects.  So that one can
choose their combination and focus on patch releases within those two named
projects.  The OP seems to as well.  Or maybe a functional subset so that
some number less than 35 may exist but, say, you cannot combine v14 and 3.0
since 3.0 since 3.2 was the most recent release of PostGIS when PostgreSQL
v14 came out.

In any case it does sound like the request by the OP is not something the
community has chosen to provide.  Which means a choice on their part - move
up PostGIS or compile from source.

David J.

Re: postgis

2022-07-20 Thread Imre Samu

>  I would expect the 35 packages implied by the version policies of those
two projects.

Based on my docker-postgis support  - the "geos" is also important.
Now Bullseye(Debian11) geos version is 3.9 - and this is likely to continue
until the end of the cycle ( so no upgrade expected to 3.10,3.11)

And the  (next) Postgis 3.3.0 Release is not enabling all new features
with the current Bullseye - Geos version:
https://git.osgeo.org/gitea/postgis/postgis/raw/tag/3.3.0beta2/NEWS

*"This version requires PostgreSQL 11 or higher, GEOS 3.6 or higher, and
Proj 5.2+.*

*Additional features are enabled if you are running GEOS 3.9+ST_MakeValid
enhancements with 3.10+, *
*numerouse additional enhancements with GEOS 3.11+. *
*Requires SFCGAL 1.4.1+ for ST_AlphaShape and ST_OptimalAlphaShape.*
*"*

And Postgis 3.2 also has some enhancements working only with geos 3.10+  (
ST_MakeValid enhancements )
And "Bookworm" Debian12 expected  >= mid-2023.
so not easy ...

Imre


David G. Johnston  ezt írta (időpont: 2022.
júl. 20., Sze, 18:31):

> On Wed, Jul 20, 2022 at 9:21 AM Imre Samu  wrote:
>
>> > My general impression is that the packaging, at least for Debian,
>> > doesn’t actually understand how the PostGIS project handles versioning
>> support.
>> > But i may be missing something
>>
>> "PostGIS Pre-built Binary Distributions for various OS"
>> --->  https://trac.osgeo.org/postgis/wiki/UsersWikiPackages
>>
>> Debian is a conservative Linux.
>>
>> IMHO:
>> Packaging is not so easy, [
>> https://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS  ]
>> - there are [n.=7] Postgres version [9.6,10,11,12,13,14,15 ]   [ now: all
>> supported in bullseye ]
>> - there are [g.=9 ] Geos version [3.3,3.4,3.5,3.6,3.7,3.8,3.9,3.10,3.11]
>> [ now: bullsey= 3.9.0 ]
>> - there are [p.=7 ] Proj version [ 4.8,4.9,5.x,6.x,7.x,8.x,9.x ][
>> now: bullseye = 7.2.1 ]
>> - there are [d.= 7 ] Gdal version [ 2.4,3.0,3.1,3.2,3.3,3.4,3.5][
>> now: bullseye = 3.2.2 ]
>> - there are [m.=5] Postgis version [2.4,2.5,3.0,3.1,3.2,3.3]   [now:
>> bullseye= 3.2.1 ]
>>
>> And there are also projects based on PostGIS.
>> - Pgrouting [r.=7 ]   [2.3,2.4,2.5,2.6,3.0,3.1,3.2,3.3]  [ now:
>> bullseye= 3.3.0 ; postgresql-12-pgrouting ]
>>
>> So the ideal "end user" combination =  n*g*p*d*m*r  = 7*9*7*7*5*7  =
>> 108045
>>
>> // disclaimer:   I am a Postgis user and a
>> https://github.com/postgis/docker-postgis contributor
>>
>>>
>>>
> Yes, my expectation may be naive, but as the package name is
> "postgresql-[version]-postgis-[version]" I would expect the 35 packages
> implied by the version policies of those two projects.  So that one can
> choose their combination and focus on patch releases within those two named
> projects.  The OP seems to as well.  Or maybe a functional subset so that
> some number less than 35 may exist but, say, you cannot combine v14 and 3.0
> since 3.0 since 3.2 was the most recent release of PostGIS when PostgreSQL
> v14 came out.
>
> In any case it does sound like the request by the OP is not something the
> community has chosen to provide.  Which means a choice on their part - move
> up PostGIS or compile from source.
>
> David J.
>
>
>

Re: Batch process


On 7/20/22 9:38 AM, Rama Krishnan wrote:

Reply to list also
Ccing list.

Hi ALL,

I have created the batch wise query but the variable is not working in 
the delete.



create or replace function sports_sale() returns void as $$
declare
    counter integer := 0;
    row_count integer :=0;
    start integer :=1;

begin

    SELECT sale_start_count INTO start FROM sale_delete_counter ORDER BY 
ID DESC LIMIT 1;
    SELECT sale_loop_counter INTO counter FROM sale_delete_counter ORDER 
BY ID DESC LIMIT 1;

    SELECT count(*) INTO row_count FROM sports_sale_archive;
    SELECT ceil(row_count/1) INTO row_count;
      while counter < row_count loop
        raise notice 'Counter %', counter;
        delete from sports_sale where id in (select id from 
sports_sale_archive WHERE id between start and start+);

        counter := counter + 1;
        start :=start+1;
        INSERT INTO 
sale_delete_counter(sale_start_count,sale_loop_counter) 
VALUES(start,counter);

        exit when counter>5;
      end loop;
end;$$ language plpgsql;


1) I thought this was a date based deletion?

2) How do you know that the ids in "id between start and start+" 
actually exist?


3) Could this not be simplified to something like?:

create or replace function sports_sale() returns void as $$
declare
   counter integer;

begin
select count(*) into counter from sports_sale_archive where 
 between  and ;


 while counter > 0 loop
   raise notice 'Counter %', counter;
   delete from sports_sale where id in (select id from 
sports_sale_archive WHERE id between  and  order by 
 limit 1);

   counter := counter - 1;
 end loop;
end;$$ language plpgsql;


Not tested and should be taken as starting point as it is not entirely 
clear to me what you are trying to achieve.



Here I have created the archive table based on created_date with more 
one year data. i want to execute this delete query using batch wise(each 
iteration 10K totally 50K records deletion per execution ). The issue 
was that the variable was not working in deletion subquery.

Regards

A.Rama Krishnan





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

plan for function returning table combined with condition

2022-07-20 Thread Thierry Henrio

Hello,

I have a table with a jsonb row, opening_times, that I need to expand for
later processing. jsonb is an object, like so {"1": [["06:00:00",
"23:59:59"]], ...}.

select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0
->> 1)::time as endt from (
  select s.id, s.name, j.* from shops s cross join
jsonb_each(s.opening_times) as j(day, value)
) t

I made a function out of this sql:

create or replace function expand_shop_opening_times() returns table(id
int, name text, day int, startt time, endt time)
as $$
select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0
->> 1)::time as endt from (
  select s.id, s.name, j.* from shops s cross join
jsonb_each(s.opening_times) as j(day, value)
) t
$$ language sql

So I can use it like so (A):

select id, name from expand_shop_opening_times() where id=1307;

The plan for statement (A) is:

 Function Scan on expand_shop_opening_times  (cost=0.25..12.75 rows=5
width=36) (actual time=15.950..16.418 rows=7 loops=1)
   Filter: (id = 1307)
   Rows Removed by Filter: 10540
 Planning Time: 0.082 ms
 Execution Time: 16.584 ms

Whereas plan for statement (B)

select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0
->> 1)::time as endt from (
  select s.id, s.name, j.* from shops s cross join
jsonb_each(s.opening_times) as j(day, value)
) t
where id=1307

I have:

 Nested Loop  (cost=0.28..12.80 rows=100 width=41) (actual
time=0.030..0.038 rows=7 loops=1)
   ->  Index Scan using shops_pkey on shops s  (cost=0.28..8.29 rows=1
width=341) (actual time=0.012..0.014 rows=1 loops=1)
 Index Cond: (id = 1307)
   ->  Function Scan on jsonb_each j  (cost=0.00..1.00 rows=100 width=64)
(actual time=0.008..0.009 rows=7 loops=1)
 Planning Time: 0.116 ms
 Execution Time: 0.062 ms

Is there are any improvement I can make to my function definition so that
planner can find a better plan for the (A) statement?

Cheers, Thierry

Re: Batch process


But the first thing he does is drop test_old.

On 7/20/22 09:52, Adrian Klaver wrote:

On 7/20/22 01:28, Ron wrote:

On 7/20/22 00:08, Rama Krishnan wrote:

Hi All,

I am doing purge activity my sales table contains 5M records I am going 
to delete more than 1 year data (which was 3M) records so it was running 
more so I want to do batch wise deletion through plsql





 created or replace function data_purge() returns void as$$
Declare
Begin
Drop table test_old;
Create table test_old as select * from sales where bill_date-interval '1 year';


Delete table sales where sales_id in (select sales_id from test_old;

End;
$$ language plpgsql;


Kindly guide me


Why not just DELETE FROM sales WHERE bill_date

Because it looks like the OP is saving the old records in test_old.




--
Angular momentum makes the world go 'round.

Re: Batch process


On 7/20/22 11:16 AM, Ron wrote:

But the first thing he does is drop test_old.


Then:

Create table test_old as select * from sales where bill_date-interval '1 year';


At that point you could do either:

Delete from sales where sales_id in (select sales_id from test_old);

or

DELETE FROM sales WHERE bill_date

On 7/20/22 09:52, Adrian Klaver wrote:

On 7/20/22 01:28, Ron wrote:

On 7/20/22 00:08, Rama Krishnan wrote:

Hi All,

I am doing purge activity my sales table contains 5M records I am 
going to delete more than 1 year data (which was 3M) records so it 
was running more so I want to do batch wise deletion through plsql





 created or replace function data_purge() returns void as$$
Declare
Begin
Drop table test_old;
Create table test_old as select * from sales where bill_date-interval '1 year';


Delete table sales where sales_id in (select sales_id from test_old;

End;
$$ language plpgsql;


Kindly guide me


Why not just DELETE FROM sales WHERE bill_dateyear';?




Because it looks like the OP is saving the old records in test_old.







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

Re: plan for function returning table combined with condition

2022-07-20 Thread Tom Lane

Thierry Henrio  writes:
> I made a function out of this sql:

> create or replace function expand_shop_opening_times() returns table(id
> int, name text, day int, startt time, endt time)
> as $$
> select id, name, day::int, (value -> 0 ->> 0)::time as startt, (value -> 0
> ->> 1)::time as endt from (
>   select s.id, s.name, j.* from shops s cross join
> jsonb_each(s.opening_times) as j(day, value)
> ) t
> $$ language sql

> So I can use it like so (A):

> select id, name from expand_shop_opening_times() where id=1307;

> The plan for statement (A) is:

>  Function Scan on expand_shop_opening_times  (cost=0.25..12.75 rows=5
> width=36) (actual time=15.950..16.418 rows=7 loops=1)
>Filter: (id = 1307)
>Rows Removed by Filter: 10540
>  Planning Time: 0.082 ms
>  Execution Time: 16.584 ms

You want this SQL function to be inlined, but it isn't being.
I think the reason is that (by default) it's VOLATILE, and
inline_set_returning_function doesn't like that:

 * Forget it if the function is not SQL-language or has other showstopper
 * properties.  In particular it mustn't be declared STRICT, since we
 * couldn't enforce that.  It also mustn't be VOLATILE, because that is
 * supposed to cause it to be executed with its own snapshot, rather than
 * sharing the snapshot of the calling query.  We also disallow returning
 * SETOF VOID, because inlining would result in exposing the actual result
 * of the function's last SELECT, which should not happen in that case.

So try adding STABLE to the function definition.

(This could be better documented, perhaps.)

regards, tom lane

Re: Batch process


How will DELETE WHERE sales_id IN (...); , given that test_old has no index?

Of course, we don't know if there's an index on sales.bill_date, since OP's 
only response has been another "tell me how to do it".


On 7/20/22 13:32, Adrian Klaver wrote:

On 7/20/22 11:16 AM, Ron wrote:

But the first thing he does is drop test_old.


Then:

Create table test_old as select * from sales where bill_date-interval '1 year';


At that point you could do either:

Delete from sales where sales_id in (select sales_id from test_old);

or

DELETE FROM sales WHERE bill_date

On 7/20/22 09:52, Adrian Klaver wrote:

On 7/20/22 01:28, Ron wrote:

On 7/20/22 00:08, Rama Krishnan wrote:

Hi All,

I am doing purge activity my sales table contains 5M records I am 
going to delete more than 1 year data (which was 3M) records so it was 
running more so I want to do batch wise deletion through plsql





 created or replace function data_purge() returns void as$$
Declare
Begin
Drop table test_old;
Create table test_old as select * from sales where bill_date-interval '1 year';


Delete table sales where sales_id in (select sales_id from test_old;

End;
$$ language plpgsql;


Kindly guide me


Why not just DELETE FROM sales WHERE bill_date

Because it looks like the OP is saving the old records in test_old.









--
Angular momentum makes the world go 'round.

Re: Batch process


On 7/20/22 11:56, Ron wrote:
How will DELETE WHERE sales_id IN (...); , given that test_old has no 
index?


Of course, we don't know if there's an index on sales.bill_date, since 
OP's only response has been another "tell me how to do it".




This awaits more information. At this point it is about the journey not 
the destination:)



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

Re: Patroni & PostgreSQL issue

2022-07-20 Thread Abdul Sayeed

Hi Uma,

If i understand your scenario correct, after failover, Patroni created
deleted files on old primary by replciating from New primary?

If that is correct, i would recommend to check lag between new primary and
old primary(now slave). if it is zero then we are good to perform failover.


Regards,
Abdul Sayeed

On Wed, Jul 20, 2022 at 5:29 PM Sankar, Uma (Uma)  wrote:

> Hi All,
>
>
>
> This is regarding the Postgres HA working with patroni in 3 node setup, we
> have an issue with the primary because a few database files were deleted
> manually so performed a switch over to move the services from primary to
> secondary with patroni, post the switchover was deleted file was re-created
> by itself in the old primary.
>
>
>
> Now we are planning to switch back the services from secondary(Leader) to
> primary(replica) as the deleted files were re-created itself, can someone
> please suggest if this works normally when we switch back to promote
> primary as the leader and secondary as a replica.
>
>
>
>
>
> *Regards,*
>
> *Uma Sankar*
>
>
>


-- 
Thanks & Regards,
Abdul Sayeed
PostgreSQL DBA
Postgres Professional Certified
Skype: abdul.sayeed24

Re: postgis