Re: Is there a way to automatically scan a table and determine the format of data

[Karsten Hilbert]

Am Wed, Feb 16, 2022 at 01:27:56AM + schrieb Shaozhong SHI:

> Is there a way to automatically scan a table and report the format of data
> for each column?

pg_class

But you may want to rethink the approach given that you use Python.

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

if not exists (SELECT 1... UNION SELECT 1...)

[Alexander Farber]

Hello,

when I search for a non existent word in the two tables hosted in
PostgreSQL 14.1 then I get zero records as expected:

words_en=> SELECT 1 FROM words_nouns WHERE word = 'ABCDE' UNION SELECT 1
FROM words_verbs WHERE word = 'ABCDE';
 ?column?
--
(0 rows)

But when I try to use the same command in my stored function, then it goes
through, as if the word would exist (and a new record is inserted into the
words_puzzle table):

CREATE OR REPLACE FUNCTION words_guess_puzzle(
in_mid   bigint,
in_socialinteger,
in_sid   text,
in_auth  text,
in_guess text,
OUT out_text text
) RETURNS text AS
$func$
DECLARE
_uid integer;
BEGIN
IF NOT words_valid_user(in_social, in_sid, in_auth) THEN
RAISE EXCEPTION 'Invalid user = % %', in_social, in_sid;
END IF;

_uid := (SELECT uid FROM words_social WHERE social = in_social AND sid
= in_sid);

in_guess := UPPER(in_guess);

-- check if the in_guess is a valid word - WHY DOES THIS NEVER TRIGGER?
IF NOT EXISTS(SELECT 1 FROM words_nouns WHERE word = in_guess UNION
  SELECT 1 FROM words_verbs WHERE word = in_guess) THEN
out_text := '___WRONG___'
RETURN;
END IF;

INSERT INTO words_puzzle (mid, uid, word, guessed)
VALUES (in_mid, _uid, in_guess, CURRENT_TIMESTAMP);

out_text := '___CORRECT___'
RETURN;
END
$func$ LANGUAGE plpgsql;

What could be the reason please?

Best regards
Alex

Re: if not exists (SELECT 1... UNION SELECT 1...)

[Laurenz Albe]

On Wed, 2022-02-16 at 11:51 +0100, Alexander Farber wrote:
> when I search for a non existent word in the two tables hosted in PostgreSQL 
> 14.1 then I get zero records as expected:
> 
> words_en=> SELECT 1 FROM words_nouns WHERE word = 'ABCDE' UNION SELECT 1 FROM 
> words_verbs WHERE word = 'ABCDE';
>  ?column?
> --
> (0 rows)
> 
> But when I try to use the same command in my stored function, then it goes 
> through,
> as if the word would exist (and a new record is inserted into the 
> words_puzzle table):
> 
> CREATE OR REPLACE FUNCTION words_guess_puzzle(
>     in_mid       bigint,
>     in_social    integer,
>     in_sid       text,
>     in_auth      text,
>     in_guess     text,
>     OUT out_text text
> ) RETURNS text AS
> $func$
> [...]
>     -- check if the in_guess is a valid word - WHY DOES THIS NEVER TRIGGER?
>     IF NOT EXISTS(SELECT 1 FROM words_nouns WHERE word = in_guess UNION
>                   SELECT 1 FROM words_verbs WHERE word = in_guess) THEN
>         out_text := '___WRONG___'
>         RETURN;
>     END IF;
> [...]
> 
> What could be the reason please?

One reason could be index corruption.  If one query uses an index and the other 
doesn't,
that could lead to different results.

The other option is of course a trivial error, like you are using a different 
search
string or connect to a different database.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Table/Parition Level Compression in Postgres

[Jagmohan Kaintura]

Hi Team,

We have a requirement where we wanted to keep the partitions holding the
old data into compressed format and the partition having the current data
in the non-compressed format.

While looking over the compression aspects in Pg 14 we have some
compression introduced for the column level with some compression methods.

Is there any other way using which we can compress the partitions data
which can be even  moved to different tablespace.

We have a implementation where we wanted to keep the 80% old data in the
compressed format as we have millions of processed CDRs.



-- 
*Best Regards,*
Jagmohan

Re: Table/Parition Level Compression in Postgres

[Laurenz Albe]

On Wed, 2022-02-16 at 18:10 +0530, Jagmohan Kaintura wrote:
> We have a requirement where we wanted to keep the partitions holding the old 
> data into compressed
> format and the partition having the current data in the non-compressed format.
> While looking over the compression aspects in Pg 14 we have some compression 
> introduced for the column level with some compression methods.
> Is there any other way using which we can compress the partitions data which 
> can be even  moved to different tablespace. 
> We have a implementation where we wanted to keep the 80% old data in the 
> compressed format as we have millions of processed CDRs.

How about a second tablespace on a file system that compresses files?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Table/Parition Level Compression in Postgres

[Jagmohan Kaintura]

Hi Laurenz

Means that is what I am thinking even right now if we can move the
partition to another tablespace and have file system level compression.

Apart from this do we have any other solution or strategy which I can try
to implement?


On Wed, Feb 16, 2022 at 6:13 PM Laurenz Albe 
wrote:

> On Wed, 2022-02-16 at 18:10 +0530, Jagmohan Kaintura wrote:
> > We have a requirement where we wanted to keep the partitions holding the
> old data into compressed
> > format and the partition having the current data in the non-compressed
> format.
> > While looking over the compression aspects in Pg 14 we have some
> compression introduced for the column level with some compression methods.
> > Is there any other way using which we can compress the partitions data
> which can be even  moved to different tablespace.
> > We have a implementation where we wanted to keep the 80% old data in the
> compressed format as we have millions of processed CDRs.
>
> How about a second tablespace on a file system that compresses files?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

-- 
*Best Regards,*
Jagmohan

Strange results when casting string to double

[Carsten Klein]

Hi there,

I'm using several (now unsupported) PostgreSQL 9.3.24 servers on 
different (ancient) Ubuntu 14.04 LTS machines. On only one of those 
servers, I get strange/wrong results when converting a string into a 
double value:


SELECT 1.56::double precision;

--> 1.55   (wrong!)

Although I do not find any differences in configuration, on all other 
servers the result looks like this (correct?):


SELECT 1.56::double precision;

--> 1.56   (correct!)

AFAIK, this conversion is done by internal function float8in, which, 
when called directly, yields the same results:


SELECT float8in('1.56');

--> 1.55   (wrong!)   on one server, and
--> 1.56   (correct!) on all other servers.

Option extra_float_digits is zero (0) while doing all these tests. Also, 
the problem seems to occur while converting text to double precision and 
not when displaying the obtained double precision value. Why? The binary 
representation of the double precision value is also different.


I've created a small to_bit function in Python to get the double 
precision value's binary representation:



CREATE OR REPLACE FUNCTION to_bit(value double precision)
  RETURNS bit AS
$BODY$
if 'fn.to_bit_d64' in SD:
return SD['fn.to_bit_d64'](value)

import struct
def to_bit_d64(value):
return ''.join('{:0>8b}'.format(c) for c in struct.pack('!d', 
value))


SD['fn.to_bit_d64'] = to_bit_d64
return SD['fn.to_bit_d64'](value)
 $BODY$
  LANGUAGE plpython3u IMMUTABLE STRICT
  COST 100;


The fraction (mantissa) of both values is different by 1:

valuefraction
1.55 10000101111010000101111010000101
1.56 10000101111010000101111010000110

The fraction of the probably wrong value is one less than the fraction 
of the correct value.


Formatting both values with 20 digits right of the decimal separator 
(like printf("%.20f" ...) yields:


1.55983124 (wrong!)
1.56005329 (correct!)

Since even calling function float8in directly returns a probably wrong 
result on one of the servers makes me believe, that there's no custom 
cast in place being responsible for the wrong results.


Function float8in basically relies on C library function

double strtod(const char *str, char **endptr)

which I tested with a tiny C programm (calling strtod only and printing 
the result with printf("%.20f", val);). The result is


1.56005329 (correct!)

on every server. So, seems like the C library function works as expected 
on all servers.


Although I'm not a C expert, I don't find anything suspicious that 
function float8in does with the value returned from strtod.


In version 9.3.24, file /src/backend/utils/adt/float.c looks a bit 
different from the file in master branch. However, basically both 
versions do much the same things. The old 9.3.24 version does some more 
special error checks (#ifdef HAVE_BUGGY_IRIX_STRTOD, #ifdef 
HAVE_BUGGY_SOLARIS_STRTOD and CHECKFLOATVAL), but these either throw 
errors or set the converted value to return to a special value (if 
indicated).


Has anyone an idea of what's going on here? I know, this version is far 
from still being supported, however, there's not much real changes in 
file float.c between these versions (in other words, this may happen 
with recent versions as well?). The database instances on all servers 
are configured quite the same (more or less). All run with the same 
extensions installed; none is using any preloaded libraries (which may 
replace C library function strtod?).


--
Carsten Klein
c(dot)klein(@)datagis(dot)com

Re: if not exists (SELECT 1... UNION SELECT 1...)

[Alexander Farber]

Hi Laurenz, thanks for your reply, but I think it is wrong -

On Wed, Feb 16, 2022 at 1:24 PM Laurenz Albe 
wrote:

> One reason could be index corruption.  If one query uses an index and the
> other doesn't,
> that could lead to different results.
>
> The other option is of course a trivial error, like you are using a
> different search
> string or connect to a different database.
>

if you go to an online PostgreSQL editor like
https://extendsclass.com/postgresql-online.html

and just enter my simple test code below,
you will see that wrongly "__CORRECT__" is printed:

DROP TABLE words_nouns;
DROP TABLE words_verbs;

CREATE TABLE words_nouns (
word text PRIMARY KEY, -- CHECK is added below
hashed text NOT NULL,
expl text
);

CREATE TABLE words_verbs (
word text PRIMARY KEY, -- CHECK is added below
hashed text NOT NULL
);

ALTER TABLE words_nouns
ADD CONSTRAINT words_nouns_word_check
CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я'
);

ALTER TABLE words_verbs
ADD CONSTRAINT words_verbs_word_check
CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я' AND
word !~ 'Ц[ЮЯ]' AND
(word ~ '[ТЧ]ЬСЯ$' OR
 word ~ '[ТЧ]Ь$' OR
 word ~ 'ТИ$')
);

CREATE OR REPLACE FUNCTION words_guess_puzzle(
in_mid   bigint,
in_guess text,
OUT out_text text
) RETURNS text AS
$func$
BEGIN
in_guess := UPPER(in_guess);

-- check if the in_guess is a valid word - WHY DOES THIS NEVER TRIGGER?
IF NOT EXISTS(SELECT 1 FROM words_nouns WHERE word = in_guess UNION
  SELECT 1 FROM words_verbs WHERE word = in_guess) THEN
out_text := '___WRONG___'
RETURN;
END IF;

out_text := '___CORRECT___'
RETURN;
END
$func$ LANGUAGE plpgsql;

SELECT words_guess_puzzle(123, 'ABCDE');

Re: if not exists (SELECT 1... UNION SELECT 1...)

[Alexander Farber]

Ah, I have to do

RETURN '___WRONG___';

and not

out_text := '___WRONG___'
RETURN;

Re: if not exists (SELECT 1... UNION SELECT 1...)

[Ralf Schuchardt]

On 16 Feb 2022, at 14:36, Alexander Farber wrote:

> Ah, I have to do
>
> RETURN '___WRONG___';

Not necessarily.

> and not
>
> out_text := '___WRONG___'
> RETURN;

You must rather end the assignment with a semicolon:

out_text := '___WRONG___';
RETURN;

Otherwise what really happens is:

out_text := '___WRONG___' RETURN;

where „return“ is a simple column name as in this select statement:

select '___WRONG___' return;


Yours,
Ralf

Re: if not exists (SELECT 1... UNION SELECT 1...)

[Alexander Farber]

Ah, thank you Ralf! That has explained it (because I was still grumbling...)

On Wed, Feb 16, 2022 at 4:44 PM Ralf Schuchardt  wrote:

> You must rather end the assignment with a semicolon:
>
> out_text := '___WRONG___';
> RETURN;
>
> Otherwise what really happens is:
>
> out_text := '___WRONG___' RETURN;
>
> where „return“ is a simple column name as in this select statement:
>
> select '___WRONG___' return;
>
>
>

Re: if not exists (SELECT 1... UNION SELECT 1...)

[Alexander Farber]

s/grumbling/wondering/

Re: Strange results when casting string to double

[Adrian Klaver]

On 2/16/22 05:27, Carsten Klein wrote:

Hi there,

I'm using several (now unsupported) PostgreSQL 9.3.24 servers on 
different (ancient) Ubuntu 14.04 LTS machines. On only one of those 
servers, I get strange/wrong results when converting a string into a 
double value:




Has anyone an idea of what's going on here? I know, this version is far 
from still being supported, however, there's not much real changes in 
file float.c between these versions (in other words, this may happen 
with recent versions as well?). The database instances on all servers 
are configured quite the same (more or less). 


What is the more or less for the problem server?

Are the hardware(CPU) architectures the same for all the servers?

If not how does the problem server differ?

Was Postgres installed from the same source/same way on all the server?


All run with the same 
extensions installed; none is using any preloaded libraries (which may 
replace C library function strtod?).





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

How to disable read-only mode on a table? (superUser)

[celati Laurent]

Good morning,

I created a new table yesterday. I am superuser and I have all the rights.
I had no worries yesterday to make editions on this table.
Today all my fields are in readonly mode. As this post illustrates:

https://bytes.com/topic/postgresql/answers/973206-how-remove-read-only-column-sign-postgresql

I tried a reboot of the postgres services. But nothing to do. If anyone
could help me ?
Thank a lot.

Re: How to disable read-only mode on a table? (superUser)

[Adrian Klaver]

On 2/16/22 02:30, celati Laurent wrote:

Good morning,

I created a new table yesterday. I am superuser and I have all the 
rights. I had no worries yesterday to make editions on this table.

Today all my fields are in readonly mode. As this post illustrates:

https://bytes.com/topic/postgresql/answers/973206-how-remove-read-only-column-sign-postgresql 



I tried a reboot of the postgres services. But nothing to do. If anyone 
could help me ?


Best bet is you don't have a PRIMARY KEY on the table.

From the screenshot I'm guessing you are using pgAdmin and if I 
remember correctly it only allows updates when there is PK field.


Just checked:

https://www.pgadmin.org/docs/pgadmin4/6.5/editgrid.html

"To modify the content of a table, each row in the table must be 
uniquely identifiable. If the table definition does not include an OID 
or a primary key, the displayed data is read only. Note that views 
cannot be edited; updatable views (using rules) are not supported."





Thank a lot.




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

Re: if not exists (SELECT 1... UNION SELECT 1...)

[Laurenz Albe]

On Wed, 2022-02-16 at 14:36 +0100, Alexander Farber wrote:
> Ah, I have to do
> 
>     RETURN '___WRONG___';
> 
> and not
> 
>         out_text := '___WRONG___'
>         RETURN;

Yes.  As I said, a trivial error.

Yours,
Laurenz Albe

Re: Strange results when casting string to double

[Carsten Klein]

On Wed, Feb 16, 2022 at 05:46 PM Adrian Klaver wrote

On 2/16/22 05:27, Carsten Klein wrote:

Hi there,

I'm using several (now unsupported) PostgreSQL 9.3.24 servers on 
different (ancient) Ubuntu 14.04 LTS machines. On only one of those 
servers, I get strange/wrong results when converting a string into a 
double value:




Has anyone an idea of what's going on here? I know, this version is 
far from still being supported, however, there's not much real changes 
in file float.c between these versions (in other words, this may 
happen with recent versions as well?). The database instances on all 
servers are configured quite the same (more or less). 




What is the more or less for the problem server?


What? Didn't get that...



Are the hardware(CPU) architectures the same for all the servers?


The problem server is a virtual machine (VMware). I've tested this on 
two other servers and do receive probably correct results. One of these 
is also a virtual machine (same VMware, running on different hardware), 
the other one is a physical box with an AMD Athlon(tm) II X4 640 64-bit 
processor.


Customer site:

Production System:  VMware (ESX Host A)   works as expected

Testing System: VMware (ESX Host B)   DOES NOT WORK as expected


Our site:

Development System: AMD Athlon(tm) II works as expected


However, since the strtod function works correctly from my tiny C 
program on all these machines, I don't believe that this problem has to 
do with hardware or architecture. I guess, PostgreSQL dynamically links 
to these C library functions, right?




If not how does the problem server differ?


I certainly have no idea. Actually, I don't see any more relevant 
differences.




Was Postgres installed from the same source/same way on all the server?


PostgreSQL was installed from Ubuntu's official repositories (14.04 LTS) 
on all servers.


--
Carsten Klein
c(dot)klein(@)datagis(dot)com

Re: Strange results when casting string to double

[David G. Johnston]

On Wed, Feb 16, 2022 at 11:38 AM Carsten Klein  wrote:

>
>
> On Wed, Feb 16, 2022 at 05:46 PM Adrian Klaver wrote
> > On 2/16/22 05:27, Carsten Klein wrote:
> >> Hi there,
> >>
> >> I'm using several (now unsupported) PostgreSQL 9.3.24 servers on
> >> different (ancient) Ubuntu 14.04 LTS machines. On only one of those
> >> servers, I get strange/wrong results when converting a string into a
> >> double value:
> >>
> >
> >> Has anyone an idea of what's going on here? I know, this version is
> >> far from still being supported, however, there's not much real changes
> >> in file float.c between these versions (in other words, this may
> >> happen with recent versions as well?). The database instances on all
> >> servers are configured quite the same (more or less).
>
>
> > What is the more or less for the problem server?
>
> What? Didn't get that...
>

You said they are more or less the same.  Problems like these tend to hide
in the "less" portion of the inequality.


> Testing System: VMware (ESX Host B)   DOES NOT WORK as expected
>
>
Given that this isn't working as expected it doesn't make for a great
testing system.  Install and initdb 14.2 on this machine and let's see what
PostgreSQL produces.


> > If not how does the problem server differ?
>
> I certainly have no idea. Actually, I don't see any more relevant
> differences.
>

What is the precise version of libc that is installed for one.  Exact ESX
releases too.

This isn't really all that interesting a report for the project if it only
exists in one ancient system that cannot be experimented with.  Maybe
it's a faulty register on that machine's CPU.  There is more
double-checking and comparing that can be done here but it seems unlikely
to be productive.  It is more plausible that the snowflake machine in
question just has issues and needs to be retired.  Installing a newer
version of PostgreSQL on it before junking it is about the right amount of
experimental effort.

David J.

Re: Strange results when casting string to double

[Carsten Klein]

On Wed, Feb 16, 2022 at 08:11 PM David G. Johnston wrote:

You said they are more or less the same.  Problems like these tend to 
hide in the "less" portion of the inequality.


On of the virtualized servers was created as a clone of the other one 
(using VMware to clone the VM). So, basically, these are very equal. Of 
course, they diverged over time.


Focusing on PostgreSQL, here are the differences of postgresql.conf, 
comparing testing system and production system:


< work_mem = 8MB# min 64kB
---
> work_mem = 4MB# min 64kB
417c417
< #log_statement = 'all'# none, ddl, mod, all
---
> #log_statement = 'none'   # none, ddl, mod, all

Both PostgreSQL server have the same roles and users, that same 
extensions installed and no preloaded libraries.


Given that this isn't working as expected it doesn't make for a great 
testing system.  Install and initdb 14.2 on this machine and let's see 
what PostgreSQL produces.


The testing system runs since 2015. I don't know whether the problem was 
present from the beginning. But I don't think so, as we also have 
"correct" double values in that database. Now, since "binary equality" 
of the double precision values is a new requirement, we started to 
notice, that (at least not) newly added (UPDATEd) values, e. g. 1.56 are 
not binary equal to already present values:


Table abc, column xyz: currently (before) 1.56

UPDATE abc SET xyz = 1.56;

Table abc, column xyz: after 1.55999

We have a trigger, that monitors such updates and it reports a changed 
value, which is not correct. The problem is, that the assignment


   SET xyz = 1.56

actually means

   SET xyz = 1.55999

since getting a double value from the string 1.56 yields 1.55999.

Yes, moving to the latest PostgreSQL version might fix that error. 
However, this is a customer's testing system. Actually, it is intended 
to be reinstalled with Ubuntu 22.04 LTS which brings PostgreSQL 14. But 
prior to that, we need to complete a project on the testing system that 
requires that "binary equality" of double values.


What is the precise version of libc that is installed for one.  Exact 
ESX releases too.


Both VM servers run on

ESXi 6   (correct behavior)
ESXi 6.5 (misbehaving)

All machines use libc version 2.19 (libc-2.19.so).

This isn't really all that interesting a report for the project if it 
only exists in one ancient system that cannot be experimented with.  
Maybe it's a faulty register on that machine's CPU.  There is more 
double-checking and comparing that can be done here but it seems 
unlikely to be productive.  It is more plausible that the snowflake 
machine in question just has issues and needs to be retired.  Installing 
a newer version of PostgreSQL on it before junking it is about the right 
amount of experimental effort.


I just wanted to ask whether someone knows something about this or has 
ever heard about such a behavior. You say, the snowflake machine has 
issues... I don't believe in hardware issues, since it runs in VMware 
and likely on many different CPUs. Isn't it more than unlikely that such 
a constantly occurring error is caused by one faulty CPU (among that 
many CPUs an ESX server typically has)?


And, keep in mind that strtod function works as expected from a simply C 
testing program. I guess that the parsed double's value gets modified 
somewhere in PostgreSQL after strtod was called. However, I do not yet 
see where and why. I was hoping that someone of you could help.


Carsten

Re: Strange results when casting string to double

[Gavan Schneider]

On 17 Feb 2022, at 8:02, Carsten Klein wrote:

> We have a trigger, that monitors such updates and it reports a changed value, 
> which is not correct. The problem is, that the assignment
>
> SET xyz = 1.56
>
> actually means
>
> SET xyz = 1.55999
>
> since getting a double value from the string 1.56 yields 1.55999.



> I just wanted to ask whether someone knows something about this or has ever 
> heard about such a behavior. You say, the snowflake machine has issues... I 
> don't believe in hardware issues, since it runs in VMware and likely on many 
> different CPUs. Isn't it more than unlikely that such a constantly occurring 
> error is caused by one faulty CPU (among that many CPUs an ESX server 
> typically has)?
>
Harking back to my long distant formative years I was taught to never ever rely 
on equality tests when it came to floating point values. Nothing has changed in 
this regard.

If binary exact is part of the requirement then the answer is INTEGER or 
NUMERIC never FLOAT, REAL, DOUBLE or anything similar.

So, assuming the trigger function is the only source of this spurious grief one 
could apply the lesson taught in the 60s from the dawn of computing:
EQUALITY = absolute_value(op1 - op2)  < epsilon — where op1 & op2 are 
DOUBLE, and epsilon is smaller than you care for

Given the intrinsic (standards compliant) uncertainty when converting from 
absolute (e.g., string representation) to floating point there will never be 
value resolving why there are differences.

I suggest using the comparison that is appropriate to the representation of 
those values or fix the design by using the proper representation.

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a 
well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920