function to_char(unknown) is not unique at character 8

[gzh]

Hi, 




I upgraded the version of PostgreSQL from 12.6 to 12.13, 

when I execute the sql below , the to_char function caused the following error.




---SQL--

select TO_CHAR('100');




ERROR:  function to_char(unknown) is not unique at character 8

HINT:  Could not choose a best candidate function. You might need to add 
explicit type casts.




There is no problem before the upgrade and to_char function comes from the 
Orafce extension.

The configuration of the old and new databases is as follows.




Database server (old): PostgreSQL 12.6（orafce3.13）

Database server (new): PostgreSQL 12.13（orafce3.24）




The new database has successfully installed the orafce 3.24 extension.

It does not occur in "PostgreSQL 12.6 and orafce 3.13", 

but occurs in "PostgreSQL 12.13 and orafce 3.24", 

so either the difference between 12.6 and 12.13 or the difference between 
orafce 3.13 and 3.24 is suspicious.




What is the reason for the problem and how to fix the error?




Regards

Re: function to_char(unknown) is not unique at character 8

[Pavel Stehule]

Hi

čt 6. 7. 2023 v 11:19 odesílatel gzh  napsal:

> Hi,
>
>
> I upgraded the version of PostgreSQL from 12.6 to 12.13,
>
> when I execute the sql below , the to_char function caused the following
> error.
>
>
> ---SQL--
>
> select TO_CHAR('100');
>
>
> ERROR:  function to_char(unknown) is not unique at character 8
>
> HINT:  Could not choose a best candidate function. You might need to add
> explicit type casts.
>
>
> There is no problem before the upgrade and to_char function comes from the
> Orafce extension.
>
> The configuration of the old and new databases is as follows.
>
>
> Database server (old): PostgreSQL 12.6（orafce3.13）
>
> Database server (new): PostgreSQL 12.13（orafce3.24）
>
>
> The new database has successfully installed the orafce 3.24 extension.
>
> It does not occur in "PostgreSQL 12.6 and orafce 3.13",
>
> but occurs in "PostgreSQL 12.13 and orafce 3.24",
>
> so either the difference between 12.6 and 12.13 or the difference between
> orafce 3.13 and 3.24 is suspicious.
>
>
> What is the reason for the problem and how to fix the error?
>

The new orafce contains to_char for numeric types and to_char for
timestamp. Old orafce had to_char function (with one argument) only for
numeric types.

This is the reason why the system doesn't know if a string of unknown type
(postgresql string literal) should be processed as numeric or as a
timestamp.

The best fix is specify used type like

(2023-07-06 13:19:33) postgres=# select oracle.TO_CHAR('100'::int);
┌─┐
│ to_char │
╞═╡
│ 100 │
└─┘
(1 row)

Regards

Pavel

>
> Regards
>
>

Re: function to_char(unknown) is not unique at character 8

[Erik Wienhold]

> On 06/07/2023 11:19 CEST gzh  wrote:
>
> I upgraded the version of PostgreSQL from 12.6 to 12.13,
> when I execute the sql below , the to_char function caused the following 
> error.
>
> ---SQL--
> select TO_CHAR('100');
>
> ERROR: function to_char(unknown) is not unique at character 8
> HINT: Could not choose a best candidate function. You might need to add 
> explicit type casts.
>
> There is no problem before the upgrade and to_char function comes from the
> Orafce extension.
> The configuration of the old and new databases is as follows.
>
> Database server (old): PostgreSQL 12.6（orafce3.13）
> Database server (new): PostgreSQL 12.13（orafce3.24）
>
> The new database has successfully installed the orafce 3.24 extension.
> It does not occur in "PostgreSQL 12.6 and orafce 3.13",
> but occurs in "PostgreSQL 12.13 and orafce 3.24",
> so either the difference between 12.6 and 12.13 or the difference between
> orafce 3.13 and 3.24 is suspicious.
>
> What is the reason for the problem and how to fix the error?

This rings a bell:

https://www.postgresql.org/message-id/1597875806.606392.1681915893771%40office.mailbox.org

Either qualify functions with namespace oracle, e.g. oracle.to_char('100'),
or place oracle on the search path.

--
Erik

Re: function to_char(unknown) is not unique at character 8

[gzh]

Thank you very much for taking the time to reply to my question. 
There are a lot of TO_CHAR in my application, and there is a high cost of 
modifying the code, 
is there another solution to solve the problem without modifying the code?







At 2023-07-06 19:21:24, "Pavel Stehule"  wrote:

Hi



čt 6. 7. 2023 v 11:19 odesílatel gzh  napsal:


Hi, 




I upgraded the version of PostgreSQL from 12.6 to 12.13, 

when I execute the sql below , the to_char function caused the following error.




---SQL--

select TO_CHAR('100');




ERROR:  function to_char(unknown) is not unique at character 8

HINT:  Could not choose a best candidate function. You might need to add 
explicit type casts.




There is no problem before the upgrade and to_char function comes from the 
Orafce extension.

The configuration of the old and new databases is as follows.




Database server (old): PostgreSQL 12.6（orafce3.13）

Database server (new): PostgreSQL 12.13（orafce3.24）




The new database has successfully installed the orafce 3.24 extension.

It does not occur in "PostgreSQL 12.6 and orafce 3.13", 

but occurs in "PostgreSQL 12.13 and orafce 3.24", 

so either the difference between 12.6 and 12.13 or the difference between 
orafce 3.13 and 3.24 is suspicious.




What is the reason for the problem and how to fix the error?



The new orafce contains to_char for numeric types and to_char for timestamp. 
Old orafce had to_char function (with one argument) only for numeric types.


This is the reason why the system doesn't know if a string of unknown type 
(postgresql string literal) should be processed as numeric or as a timestamp.


The best fix is specify used type like


(2023-07-06 13:19:33) postgres=# select oracle.TO_CHAR('100'::int);
┌─┐
│ to_char │
╞═╡
│ 100 │
└─┘
(1 row)


Regards


Pavel





Regards

Re: function to_char(unknown) is not unique at character 8

[Pavel Stehule]

čt 6. 7. 2023 v 13:38 odesílatel gzh  napsal:

> Thank you very much for taking the time to reply to my question.
> There are a lot of TO_CHAR in my application, and there is a high cost of
> modifying the code,
> is there another solution to solve the problem without modifying the code?
>

There is one dirty workaround, and because it is dirty, I don't want to
push it to orafce.

You can create own function to_char

CREATE OR REPLACE FUNCTION oracle.to_char(text)
RETURNS text AS $$
SELECT $1
$$ LANGUAGE sql IMMUTABLE STRICT;

This version will be preferred and fix this issue. On second thought, the
behavior can be a little bit different than before.

I have a question. Why do you use the to_char(string) function? Instead
to_char('text') you can write only 'text'.



>
>
> At 2023-07-06 19:21:24, "Pavel Stehule"  wrote:
>
> Hi
>
> čt 6. 7. 2023 v 11:19 odesílatel gzh  napsal:
>
>> Hi,
>>
>>
>> I upgraded the version of PostgreSQL from 12.6 to 12.13,
>>
>> when I execute the sql below , the to_char function caused the following
>> error.
>>
>>
>> ---SQL--
>>
>> select TO_CHAR('100');
>>
>>
>> ERROR:  function to_char(unknown) is not unique at character 8
>>
>> HINT:  Could not choose a best candidate function. You might need to add
>> explicit type casts.
>>
>>
>> There is no problem before the upgrade and to_char function comes from
>> the Orafce extension.
>>
>> The configuration of the old and new databases is as follows.
>>
>>
>> Database server (old): PostgreSQL 12.6（orafce3.13）
>>
>> Database server (new): PostgreSQL 12.13（orafce3.24）
>>
>>
>> The new database has successfully installed the orafce 3.24 extension.
>>
>> It does not occur in "PostgreSQL 12.6 and orafce 3.13",
>>
>> but occurs in "PostgreSQL 12.13 and orafce 3.24",
>>
>> so either the difference between 12.6 and 12.13 or the difference between
>> orafce 3.13 and 3.24 is suspicious.
>>
>>
>> What is the reason for the problem and how to fix the error?
>>
>
> The new orafce contains to_char for numeric types and to_char for
> timestamp. Old orafce had to_char function (with one argument) only for
> numeric types.
>
> This is the reason why the system doesn't know if a string of unknown type
> (postgresql string literal) should be processed as numeric or as a
> timestamp.
>
> The best fix is specify used type like
>
> (2023-07-06 13:19:33) postgres=# select oracle.TO_CHAR('100'::int);
> ┌─┐
> │ to_char │
> ╞═╡
> │ 100 │
> └─┘
> (1 row)
>
> Regards
>
> Pavel
>
>>
>> Regards
>>
>>

Logical replication restarts

[Paula Berenguel]

Hi
I am using logical replication to move an on premises database PG11 to a
cloud based PG11 and experiencing constant restart of the replication.

The replication does it again. The validation_entry started again:

2023-07-06 01:49:46.968 UTC-64a61dba.1ad8LOG: 0: logical
replication table synchronization worker for subscription "sub", table
"ZZZYYY" has started

2023-07-04 21:10:36.478 UTC-64a48b93.961LOG: 0: logical replication
table synchronization worker for subscription "sub", table " ZZZYYY" has
started


Can someone help me to find a fix for this?


Thanks

Paula

Re: Logical replication restarts

[Mateusz Henicz]

Hi,
There should be another line in the log for why the logical replication
worker has stopped in the first place. Something like Primary Key or Unique
Constraint violation, timeout on WAL sender or receiver process, etc.
Unless you can provide that information I doubt someone will be able to
help here.

Check logs on your both publisher and subscriber, with this information you
should be able to find the solution.

Cheers,
Mateusz

czw., 6 lip 2023 o 14:34 Paula Berenguel 
napisał(a):

> Hi
> I am using logical replication to move an on premises database PG11 to a
> cloud based PG11 and experiencing constant restart of the replication.
>
> The replication does it again. The validation_entry started again:
>
> 2023-07-06 01:49:46.968 UTC-64a61dba.1ad8LOG: 0: logical
> replication table synchronization worker for subscription "sub", table
> "ZZZYYY" has started
>
> 2023-07-04 21:10:36.478 UTC-64a48b93.961LOG: 0: logical
> replication table synchronization worker for subscription "sub",
> table " ZZZYYY" has started
>
>
> Can someone help me to find a fix for this?
>
>
> Thanks
>
> Paula
>

Re: function to_char(unknown) is not unique at character 8

[Ron]

On 7/6/23 04:19, gzh wrote:


Hi,


I upgraded the version of PostgreSQL from 12.6 to 12.13,

when I execute the sql below , the to_char function caused the following 
error.



---SQL--

select TO_CHAR('100');



Isn't '100' already a character string?

How do you convert a character string to a character string?

--
Born in Arizona, moved to Babylonia.

Re: function to_char(unknown) is not unique at character 8

[Tom Lane]

Ron  writes:
> On 7/6/23 04:19, gzh wrote:
>> when I execute the sql below , the to_char function caused the following 
>> error.
>> select TO_CHAR('100');

> Isn't '100' already a character string?

Nope; in the mind of the Postgres parser, it's a literal of unknown type,
with the actual type to be inferred from context.  There is a small
preference for resolving such a thing as being of text type, but only a
small one.  The rules are exactly the same as for an undecorated NULL
constant.

regards, tom lane

Re: function to_char(unknown) is not unique at character 8

[gzh]

Thank you very much for taking the time to reply to my question. 
I added oracle to search_path, but it didn't work.


postgres=# show search_path;
 search_path
-
 "$user", public, oracle, pg_catalog



















At 2023-07-06 19:36:49, "Erik Wienhold"  wrote:
>> On 06/07/2023 11:19 CEST gzh  wrote:
>>
>> I upgraded the version of PostgreSQL from 12.6 to 12.13,
>> when I execute the sql below , the to_char function caused the following 
>> error.
>>
>> ---SQL--
>> select TO_CHAR('100');
>>
>> ERROR: function to_char(unknown) is not unique at character 8
>> HINT: Could not choose a best candidate function. You might need to add 
>> explicit type casts.
>>
>> There is no problem before the upgrade and to_char function comes from the
>> Orafce extension.
>> The configuration of the old and new databases is as follows.
>>
>> Database server (old): PostgreSQL 12.6（orafce3.13）
>> Database server (new): PostgreSQL 12.13（orafce3.24）
>>
>> The new database has successfully installed the orafce 3.24 extension.
>> It does not occur in "PostgreSQL 12.6 and orafce 3.13",
>> but occurs in "PostgreSQL 12.13 and orafce 3.24",
>> so either the difference between 12.6 and 12.13 or the difference between
>> orafce 3.13 and 3.24 is suspicious.
>>
>> What is the reason for the problem and how to fix the error?
>
>This rings a bell:
>
>https://www.postgresql.org/message-id/1597875806.606392.1681915893771%40office.mailbox.org
>
>Either qualify functions with namespace oracle, e.g. oracle.to_char('100'),
>or place oracle on the search path.
>
>--
>Erik

Re: function to_char(unknown) is not unique at character 8

[Pavel Stehule]

čt 6. 7. 2023 v 16:16 odesílatel gzh  napsal:

> Thank you very much for taking the time to reply to my question.
> I added oracle to search_path, but it didn't work.
>
> postgres=# show search_path;
>  search_path
> -
>  "$user", public, oracle, pg_catalog
>

this is different issue


>
>
>
>
>
>
>
> At 2023-07-06 19:36:49, "Erik Wienhold"  wrote:
> >> On 06/07/2023 11:19 CEST gzh  wrote:
> >>
> >> I upgraded the version of PostgreSQL from 12.6 to 12.13,
> >> when I execute the sql below , the to_char function caused the following 
> >> error.
> >>
> >> ---SQL--
> >> select TO_CHAR('100');
> >>
> >> ERROR: function to_char(unknown) is not unique at character 8
> >> HINT: Could not choose a best candidate function. You might need to add 
> >> explicit type casts.
> >>
> >> There is no problem before the upgrade and to_char function comes from the
> >> Orafce extension.
> >> The configuration of the old and new databases is as follows.
> >>
> >> Database server (old): PostgreSQL 12.6（orafce3.13）
> >> Database server (new): PostgreSQL 12.13（orafce3.24）
> >>
> >> The new database has successfully installed the orafce 3.24 extension.
> >> It does not occur in "PostgreSQL 12.6 and orafce 3.13",
> >> but occurs in "PostgreSQL 12.13 and orafce 3.24",
> >> so either the difference between 12.6 and 12.13 or the difference between
> >> orafce 3.13 and 3.24 is suspicious.
> >>
> >> What is the reason for the problem and how to fix the error?
> >
> >This rings a bell:
> >
> >https://www.postgresql.org/message-id/1597875806.606392.1681915893771%40office.mailbox.org
> >
> >Either qualify functions with namespace oracle, e.g. 
> >oracle.to_char('100'),
> >or place oracle on the search path.
> >
> >--
> >Erik
>
>

Re: function to_char(unknown) is not unique at character 8

[gzh]

Thank you for the solution, it works fine.




> I have a question. Why do you use the to_char(string) function? Instead 
> to_char('text') you can write only 'text'.
I'm guessing it's probably a bug made by the original developer,
but I'm not sure how many bugs there are, because it works fine in older 
version(orafce 3.13).







At 2023-07-06 19:54:19, "Pavel Stehule"  wrote:





čt 6. 7. 2023 v 13:38 odesílatel gzh  napsal:

Thank you very much for taking the time to reply to my question. 
There are a lot of TO_CHAR in my application, and there is a high cost of 
modifying the code, 
is there another solution to solve the problem without modifying the code?


There is one dirty workaround, and because it is dirty, I don't want to push it 
to orafce.


You can create own function to_char


CREATE OR REPLACE FUNCTION oracle.to_char(text)
RETURNS text AS $$
SELECT $1
$$ LANGUAGE sql IMMUTABLE STRICT;


This version will be preferred and fix this issue. On second thought, the 
behavior can be a little bit different than before.



I have a question. Why do you use the to_char(string) function? Instead 
to_char('text') you can write only 'text'.



 








At 2023-07-06 19:21:24, "Pavel Stehule"  wrote:

Hi



čt 6. 7. 2023 v 11:19 odesílatel gzh  napsal:


Hi, 




I upgraded the version of PostgreSQL from 12.6 to 12.13, 

when I execute the sql below , the to_char function caused the following error.




---SQL--

select TO_CHAR('100');




ERROR:  function to_char(unknown) is not unique at character 8

HINT:  Could not choose a best candidate function. You might need to add 
explicit type casts.




There is no problem before the upgrade and to_char function comes from the 
Orafce extension.

The configuration of the old and new databases is as follows.




Database server (old): PostgreSQL 12.6（orafce3.13）

Database server (new): PostgreSQL 12.13（orafce3.24）




The new database has successfully installed the orafce 3.24 extension.

It does not occur in "PostgreSQL 12.6 and orafce 3.13", 

but occurs in "PostgreSQL 12.13 and orafce 3.24", 

so either the difference between 12.6 and 12.13 or the difference between 
orafce 3.13 and 3.24 is suspicious.




What is the reason for the problem and how to fix the error?



The new orafce contains to_char for numeric types and to_char for timestamp. 
Old orafce had to_char function (with one argument) only for numeric types.


This is the reason why the system doesn't know if a string of unknown type 
(postgresql string literal) should be processed as numeric or as a timestamp.


The best fix is specify used type like


(2023-07-06 13:19:33) postgres=# select oracle.TO_CHAR('100'::int);
┌─┐
│ to_char │
╞═╡
│ 100 │
└─┘
(1 row)


Regards


Pavel





Regards

Re: function to_char(unknown) is not unique at character 8

[Pavel Stehule]

čt 6. 7. 2023 v 16:28 odesílatel gzh  napsal:

> Thank you for the solution, it works fine.
>
>
> > I have a question. Why do you use the to_char(string) function? Instead
> to_char('text') you can write only 'text'.
> I'm guessing it's probably a bug made by the original developer,
> but I'm not sure how many bugs there are, because it works fine in older
> version(orafce 3.13).
>

you can use another version

CREATE OR REPLACE FUNCTION oracle.to_char(text)
RETURNS text AS $$
BEGIN
  RAISE WARNING 'using useless to_char function';
  RETURN $1;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

This can help to identify these useless usage of this function

Unfortunately, the Oracle type system is very different, so it is hard to
emulate in Postgres. And Oracle PL/SQL design allows people to write
terribly ugly code. These bad things are hard to repeat in Pg.


>
>
> At 2023-07-06 19:54:19, "Pavel Stehule"  wrote:
>
>
>
> čt 6. 7. 2023 v 13:38 odesílatel gzh  napsal:
>
>> Thank you very much for taking the time to reply to my question.
>> There are a lot of TO_CHAR in my application, and there is a high cost of
>> modifying the code,
>> is there another solution to solve the problem without modifying the code?
>>
>
> There is one dirty workaround, and because it is dirty, I don't want to
> push it to orafce.
>
> You can create own function to_char
>
> CREATE OR REPLACE FUNCTION oracle.to_char(text)
> RETURNS text AS $$
> SELECT $1
> $$ LANGUAGE sql IMMUTABLE STRICT;
>
> This version will be preferred and fix this issue. On second thought, the
> behavior can be a little bit different than before.
>
> I have a question. Why do you use the to_char(string) function? Instead
> to_char('text') you can write only 'text'.
>
>
>
>>
>>
>> At 2023-07-06 19:21:24, "Pavel Stehule"  wrote:
>>
>> Hi
>>
>> čt 6. 7. 2023 v 11:19 odesílatel gzh  napsal:
>>
>>> Hi,
>>>
>>>
>>> I upgraded the version of PostgreSQL from 12.6 to 12.13,
>>>
>>> when I execute the sql below , the to_char function caused the following
>>> error.
>>>
>>>
>>> ---SQL--
>>>
>>> select TO_CHAR('100');
>>>
>>>
>>> ERROR:  function to_char(unknown) is not unique at character 8
>>>
>>> HINT:  Could not choose a best candidate function. You might need to add
>>> explicit type casts.
>>>
>>>
>>> There is no problem before the upgrade and to_char function comes from
>>> the Orafce extension.
>>>
>>> The configuration of the old and new databases is as follows.
>>>
>>>
>>> Database server (old): PostgreSQL 12.6（orafce3.13）
>>>
>>> Database server (new): PostgreSQL 12.13（orafce3.24）
>>>
>>>
>>> The new database has successfully installed the orafce 3.24 extension.
>>>
>>> It does not occur in "PostgreSQL 12.6 and orafce 3.13",
>>>
>>> but occurs in "PostgreSQL 12.13 and orafce 3.24",
>>>
>>> so either the difference between 12.6 and 12.13 or the difference
>>> between orafce 3.13 and 3.24 is suspicious.
>>>
>>>
>>> What is the reason for the problem and how to fix the error?
>>>
>>
>> The new orafce contains to_char for numeric types and to_char for
>> timestamp. Old orafce had to_char function (with one argument) only for
>> numeric types.
>>
>> This is the reason why the system doesn't know if a string of unknown
>> type (postgresql string literal) should be processed as numeric or as a
>> timestamp.
>>
>> The best fix is specify used type like
>>
>> (2023-07-06 13:19:33) postgres=# select oracle.TO_CHAR('100'::int);
>> ┌─┐
>> │ to_char │
>> ╞═╡
>> │ 100 │
>> └─┘
>> (1 row)
>>
>> Regards
>>
>> Pavel
>>
>>>
>>> Regards
>>>
>>>

Moving data from huge table slow, min() query on indexed column taking 38s

[Dimitrios Apostolou]

I have a huge (10G rows) table "tbl_legacy" named test_runs that I want to
PARTITION BY RANGE on an indexed integer column.

I have created the new partitioned table "tbl" and 1000 partitions in it,
so that it can take my existing data and have the partitions ready for the
next year (so most of the new partitions will be empty for now).

On the topic of bulk-transferring the data from tbl_legacy to tbl:

+ First I chose the method to DELETE...INSERT everything.

  I noticed that the postgres process was growing without bounds, up to
  the point that it consumed almost all of swap space and I had to kill
  the command. Any idea why that happens?

+ Then I broke the command above in many small chunks

  WITH rows AS (
DELETE FROM tbl_legacy AS t
WHERE (partition_key_column < $1)
RETURNING t.*
) INSERT INTO tbl SELECT * FROM rows;
  COMMIT;

  I increase the parameter $1 and keep going in a loop.  At first this
  goes OK, after one day though I notice that it has slowed down
  significantly. My investigation shows that the simple query

SELECT min(partition_key_column) from tbl_legacy;

  takes 38s, despite having an index on the column! A VACUUM fixes that,
  so I guess the index has a ton of dead tuples. I guess autovacuum does
  not do its job because the table is constantly busy.

  Unfortunately VACUUM takes long (several hours) on this huge table, so I
  can't add in the loop after the DELETE command.

  Is there a better way to avoid the bloat in the loop above?  Why can't
  the DELETE command update the index by pointing the minimum element
  beyond the dead tuples?

+ Finally I resorted to just copying the data:

INSERT INTO tbl SELECT * FROM tbl_legacy;

  This took half a day or so but worked without blowing up the memory
  usage like the 1st query.  Why is that?

  By the way, I don't consider the last solution ideal, since the data is
  duplicated between the two tables and it's harder to implement
  workarounds to do it without taking downtime. But it worked for me for
  this time. Any other ideas for partitioning an existing huge table?


Thanks in advance,
Dimitris

Re: Moving data from huge table slow, min() query on indexed column taking 38s

[Dimitrios Apostolou]

On Thu, 6 Jul 2023, Dimitrios Apostolou wrote:


+ First I chose the method to DELETE...INSERT everything.


Just to clarify, the query looked more like

  WITH rows AS ( DELETE FROM tbl_legacy RETURNING * )
INSERT INTO tbl
  SELECT * FROM rows;


  I noticed that the postgres process was growing without bounds, up to
  the point that it consumed almost all of swap space and I had to kill
  the command. Any idea why that happens?


Also note that my settings for work_mem, temp_buffers, shared_buffers etc
are all well below the RAM size and postgres has never shown unbound
growth again. Postgres version is 15.2.


Dimitris

Need help

[aarthi rajagopal]

Hi,

   I have doubt about how to enable pg_cron in PgAdmin4 version 7, kindly
guide me how to do it..

Thanks& Regards
Aarthi Rajagopal

Re: Need help

[Adrian Klaver]

On 7/5/23 21:52, aarthi rajagopal wrote:

Hi,
    I have doubt about how to enable pg_cron in PgAdmin4 version 7, 
kindly guide me how to do it..


pg_cron:

https://github.com/citusdata/pg_cron

is an extension that has nothing to do with pgAdmin4.

Follow the instructions at the link above to install it.



Thanks& Regards
Aarthi Rajagopal


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

Re: Moving data from huge table slow, min() query on indexed column taking 38s

[Lorusso Domenico]

Hello,
this is a standard problem during bulk copy.

here  some suggestions;
for example disable indexes.

The main issue is related to index, lock escalation and log writing.
In other dbms you should set log off on the table, but postgresql does not
seem to have this feature.

Anyway, using an explicit lock table exclusive should prevent lock
escalation.

So: disable indexes in target table
lock exclusive both table
insert data
truncate old table

If this doesn't work you can consider using the copy command.







Il giorno gio 6 lug 2023 alle ore 18:12 Dimitrios Apostolou 
ha scritto:

> On Thu, 6 Jul 2023, Dimitrios Apostolou wrote:
>
> > + First I chose the method to DELETE...INSERT everything.
>
> Just to clarify, the query looked more like
>
>WITH rows AS ( DELETE FROM tbl_legacy RETURNING * )
>  INSERT INTO tbl
>SELECT * FROM rows;
>
> >   I noticed that the postgres process was growing without bounds, up to
> >   the point that it consumed almost all of swap space and I had to kill
> >   the command. Any idea why that happens?
>
> Also note that my settings for work_mem, temp_buffers, shared_buffers etc
> are all well below the RAM size and postgres has never shown unbound
> growth again. Postgres version is 15.2.
>
>
> Dimitris
>
>
>
>

-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

[bug]? insert returning composite type fails

[Lorusso Domenico]

Hello guys,
In my db (version 15) I've defined a composite type with some domains

CREATE DOMAIN my_feat.audit_record_jsonb_domain
AS jsonb
NOT NULL;

ALTER DOMAIN my_feat.audit_record_jsonb_domain OWNER TO postgres;

CREATE DOMAIN my_feat.boolean_true_domain
AS boolean
DEFAULT true
NOT NULL;

ALTER DOMAIN my_feat.boolean_true_domain OWNER TO postgres;
CREATE TYPE my_feat.bitemporal_record AS
(
user_ts_range tstzrange,
db_ts_range tstzrange,
has_future_record timestamp with time zone,
audit_record my_feat.audit_record_jsonb_domain,
is_valid my_feat.boolean_true_domain
);

ALTER TYPE my_feat.bitemporal_record
OWNER TO postgres;
So I've a table like that:
CREATE TABLE IF NOT EXISTS my_feat.try_bt_info
(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START
1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
bt_info my_feat.bitemporal_record,
CONSTRAINT try_bt_info_pk PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS my_feat.try_bt_info
OWNER to postgres;

When I try an insert asking to return bt_info I catch and error; here the
example:

do $$
declare
_bt_info my_feat.bitemporal_record;
begin
insert into my_feat.try_bt_info (bt_info)
values (row(
tstzrange(now(),'infinity','[)')
, tstzrange(now(),'infinity','[)')
, null
, '{"user_id":"alpha"}'
, true)::my_feat.bitemporal_record
)
returning bt_info into _bt_info;

raise notice '%', _bt_info;
end;
$$;

Error:
ERROR: Too many commas.malformed range literal: "("[""2023-07-06
23:50:30.991122+02"",infinity)","[""2023-07-06
23:50:30.991122+02"",infinity)",,"{""user_id"": ""alpha""}",t)" ERROR:
malformed range literal: "("[""2023-07-06
23:50:30.991122+02"",infinity)","[""2023-07-06
23:50:30.991122+02"",infinity)",,"{""user_id"": ""alpha""}",t)" SQL state:
22P02
Detail: Too many commas.

It seems to be a bug, but maybe there is a workaround; any idea?

-- 
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

Re: [bug]? insert returning composite type fails

[David G. Johnston]

On Thursday, July 6, 2023, Lorusso Domenico  wrote:

>
> returning bt_info into _bt_info;
>
>
I think it’s “returning (bt_info).* into _bt_info;”

David J.

Re: [bug]? insert returning composite type fails

[Adrian Klaver]

On 7/6/23 14:52, Lorusso Domenico wrote:

Hello guys,
In my db (version 15) I've defined a composite type with some domains

CREATE DOMAIN my_feat.audit_record_jsonb_domain
     AS jsonb
     NOT NULL;

ALTER DOMAIN my_feat.audit_record_jsonb_domain OWNER TO postgres;

CREATE DOMAIN my_feat.boolean_true_domain
     AS boolean
     DEFAULT true
     NOT NULL;

ALTER DOMAIN my_feat.boolean_true_domain OWNER TO postgres;
CREATE TYPE my_feat.bitemporal_record AS
(
user_ts_range tstzrange,
db_ts_range tstzrange,
has_future_record timestamp with time zone,
audit_record my_feat.audit_record_jsonb_domain,
is_valid my_feat.boolean_true_domain
);

ALTER TYPE my_feat.bitemporal_record
     OWNER TO postgres;
So I've a table like that:
CREATE TABLE IF NOT EXISTS my_feat.try_bt_info
(
     id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 
START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),

     bt_info my_feat.bitemporal_record,
     CONSTRAINT try_bt_info_pk PRIMARY KEY (id)
)


Seems a long way around to arrive at:

CREATE TABLE IF NOT EXISTS my_feat.try_bt_info
(
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 
1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ) PRIMARY KEY,

user_ts_range tstzrange,
db_ts_range tstzrange,
has_future_record timestamp with time zone,
is_valid boolean NOT NULL 't'
);



--
Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]


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

Re: function to_char(unknown) is not unique at character 8

[gzh]

Thank you for the solution you provided to identify these useless usage of this 
function，I learned a lot from it.













At 2023-07-06 22:42:38, "Pavel Stehule"  wrote:





čt 6. 7. 2023 v 16:28 odesílatel gzh  napsal:


Thank you for the solution, it works fine.




> I have a question. Why do you use the to_char(string) function? Instead 
> to_char('text') you can write only 'text'.
I'm guessing it's probably a bug made by the original developer,
but I'm not sure how many bugs there are, because it works fine in older 
version(orafce 3.13).


you can use another version


CREATE OR REPLACE FUNCTION oracle.to_char(text)
RETURNS text AS $$
BEGIN
  RAISE WARNING 'using useless to_char function';
  RETURN $1;
END;
$$ LANGUAGE plpgsql IMMUTABLE;


This can help to identify these useless usage of this function


Unfortunately, the Oracle type system is very different, so it is hard to 
emulate in Postgres. And Oracle PL/SQL design allows people to write terribly 
ugly code. These bad things are hard to repeat in Pg.

 








At 2023-07-06 19:54:19, "Pavel Stehule"  wrote:





čt 6. 7. 2023 v 13:38 odesílatel gzh  napsal:

Thank you very much for taking the time to reply to my question. 
There are a lot of TO_CHAR in my application, and there is a high cost of 
modifying the code, 
is there another solution to solve the problem without modifying the code?


There is one dirty workaround, and because it is dirty, I don't want to push it 
to orafce.


You can create own function to_char


CREATE OR REPLACE FUNCTION oracle.to_char(text)
RETURNS text AS $$
SELECT $1
$$ LANGUAGE sql IMMUTABLE STRICT;


This version will be preferred and fix this issue. On second thought, the 
behavior can be a little bit different than before.



I have a question. Why do you use the to_char(string) function? Instead 
to_char('text') you can write only 'text'.



 








At 2023-07-06 19:21:24, "Pavel Stehule"  wrote:

Hi



čt 6. 7. 2023 v 11:19 odesílatel gzh  napsal:


Hi, 




I upgraded the version of PostgreSQL from 12.6 to 12.13, 

when I execute the sql below , the to_char function caused the following error.




---SQL--

select TO_CHAR('100');




ERROR:  function to_char(unknown) is not unique at character 8

HINT:  Could not choose a best candidate function. You might need to add 
explicit type casts.




There is no problem before the upgrade and to_char function comes from the 
Orafce extension.

The configuration of the old and new databases is as follows.




Database server (old): PostgreSQL 12.6（orafce3.13）

Database server (new): PostgreSQL 12.13（orafce3.24）




The new database has successfully installed the orafce 3.24 extension.

It does not occur in "PostgreSQL 12.6 and orafce 3.13", 

but occurs in "PostgreSQL 12.13 and orafce 3.24", 

so either the difference between 12.6 and 12.13 or the difference between 
orafce 3.13 and 3.24 is suspicious.




What is the reason for the problem and how to fix the error?



The new orafce contains to_char for numeric types and to_char for timestamp. 
Old orafce had to_char function (with one argument) only for numeric types.


This is the reason why the system doesn't know if a string of unknown type 
(postgresql string literal) should be processed as numeric or as a timestamp.


The best fix is specify used type like


(2023-07-06 13:19:33) postgres=# select oracle.TO_CHAR('100'::int);
┌─┐
│ to_char │
╞═╡
│ 100 │
└─┘
(1 row)


Regards


Pavel





Regards