Re: Can I tell libpq to connect to the primary?

[Michael Paquier]

On Thu, Apr 23, 2020 at 08:54:35AM +0200, Christian Ramseyer wrote:
> Never mind, after RTFM'ing to the very end of
> https://www.postgresql.org/docs/current/libpq-connect.html I have
> discovered target_session_attrs=read-write|any which seems to do exactly
> what I want.

Yes, as long as you don't have as requirement to make sure to connect
to a standby session this would not be a problem.  Supporting a
read-only mode for target_session_attrs is a problem we have been
stuck on for some time because of the potential issues you could face
when attempting to connect a cluster that has just been freshly
promoted: when connecting to the server libpq may see the connection
as read-only but if the standby gets promoted that could become
incorrect.
--
Michael


signature.asc
Description: PGP signature

Weird behaviour of C extension function

[Amaury Bouchard]

Hello everybody,

I have a really strange behaviour with a C function, wich gets a text as
parameter.
Everything works fine when I call the function directly, giving a text
string as parameter. But a problem occurs when I try to read data from a
table.

To illustrate the problem, I stripped the function down to the minimum. The
source code is below, but first, here is the behaviour :

Direct call
---
> select passthru('hello world!'), passthru('utf8 çhàràtérs'), passthru('
h3110 123 456 ');
INFO:  INPUT STRING: 'hello world!' (12)
INFO:  INPUT STRING: 'utf8 çhàràtérs' (18)
INFO:  INPUT STRING: ' h3110 123 456 ' (15)

(as you can see, the log messages show the correct input, with the number
of bytes between parentheses)

Reading a table data

> create table mytable ( str text);
> insert into mytable (str) values ('hello world!'), ('utf8 çhàràtérs'), ('
h3110 123 456 ');
> select passthru(str) from mytable;
INFO:  INPUT STRING: 'lo world!' (12)
INFO:  INPUT STRING: '8 çhàràtérs' (18)
INFO:  INPUT STRING: '110 123 456 �
' (15)
INFO:  INPUT STRING: '��' (5)
INFO:  INPUT STRING: '' (3)

There, you can see that the pointer seems to be shifted 3 bytes farther.

Do you have any clue for this strange behaviour?


The source code
---

#include "postgres.h"
#include "fmgr.h"
#include "funcapi.h"

// PG module init
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
void _PG_init(void);
Datum passthru(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(passthru);

void _PG_init() {
}

Datum passthru(PG_FUNCTION_ARGS) {
// get the input string
text *input = PG_GETARG_TEXT_PP(0);
char *input_pt = (char*)VARDATA(input);
int32 input_len = VARSIZE_ANY_EXHDR(input);
// create a null terminated copy of the input string
char *str_copy = calloc(1, input_len + 1);
memcpy(str_copy, input_pt, input_len);
// log message
elog(INFO, "INPUT STRING: '%s' (%d)", str_copy, input_len);
free(str_copy);
PG_RETURN_NULL();
}



Thank you.
Best regards,

Amaury Bouchard

Re: Weird behaviour of C extension function

[Laurenz Albe]

On Fri, 2020-04-24 at 14:53 +0200, Amaury Bouchard wrote:
> I have a really strange behaviour with a C function, wich gets a text as 
> parameter.
> Everything works fine when I call the function directly, giving a text string 
> as parameter. But a problem occurs when I try to read data from a table.
> 
> To illustrate the problem, I stripped the function down to the minimum. The 
> source code is below, but first, here is the behaviour :
> 
> Direct call
> ---
> > select passthru('hello world!'), passthru('utf8 çhàràtérs'), passthru(' 
> > h3110 123 456 ');
> INFO:  INPUT STRING: 'hello world!' (12)
> INFO:  INPUT STRING: 'utf8 çhàràtérs' (18)
> INFO:  INPUT STRING: ' h3110 123 456 ' (15)
> 
> (as you can see, the log messages show the correct input, with the number of 
> bytes between parentheses)
> 
> Reading a table data
> 
> > create table mytable ( str text);
> > insert into mytable (str) values ('hello world!'), ('utf8 çhàràtérs'), (' 
> > h3110 123 456 ');
> > select passthru(str) from mytable;
> INFO:  INPUT STRING: 'lo world!' (12)
> INFO:  INPUT STRING: '8 çhàràtérs' (18)
> INFO:  INPUT STRING: '110 123 456 �
> ' (15)
> INFO:  INPUT STRING: '��' (5)
> INFO:  INPUT STRING: '' (3)
> 
> There, you can see that the pointer seems to be shifted 3 bytes farther.
> 
> Do you have any clue for this strange behaviour?
> 
> 
> The source code
> ---
> 
> #include "postgres.h"
> #include "fmgr.h"
> #include "funcapi.h"
> 
> // PG module init
> #ifdef PG_MODULE_MAGIC
> PG_MODULE_MAGIC;
> #endif
> void _PG_init(void);
> Datum passthru(PG_FUNCTION_ARGS);
> PG_FUNCTION_INFO_V1(passthru);
> 
> void _PG_init() {
> }
> 
> Datum passthru(PG_FUNCTION_ARGS) {
> // get the input string
> text *input = PG_GETARG_TEXT_PP(0);
> char *input_pt = (char*)VARDATA(input);
> int32 input_len = VARSIZE_ANY_EXHDR(input);
> // create a null terminated copy of the input string
> char *str_copy = calloc(1, input_len + 1); 
> memcpy(str_copy, input_pt, input_len);
> // log message
> elog(INFO, "INPUT STRING: '%s' (%d)", str_copy, input_len);
> free(str_copy);
> PG_RETURN_NULL();
> }

You find this in "postgres.h":

 * In consumers oblivious to data alignment, call PG_DETOAST_DATUM_PACKED(),
 * VARDATA_ANY(), VARSIZE_ANY() and VARSIZE_ANY_EXHDR().  Elsewhere, call
 * PG_DETOAST_DATUM(), VARDATA() and VARSIZE().  Directly fetching an int16,
 * int32 or wider field in the struct representing the datum layout requires
 * aligned data.  memcpy() is alignment-oblivious, as are most operations on
 * datatypes, such as text, whose layout struct contains only char fields.

So you should use VARDATA_ANY.

What happens is that these short text columns have a 1-byte TOAST header,
but you ship the first 4 bytes unconditionally, assuming they were detoasted.

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

create index insist on 2 workers only

[Radoslav Nedyalkov]

Hello everybody,

We 're rebuilding a big table which has set parallel_workers = 6
system has
 max_parallel_maintenance_workers | 6   |
/var/lib/pgsql/11/data/postgresql.sumup.conf
 max_parallel_workers | 16  |
/var/lib/pgsql/11/data/postgresql.sumup.conf
 max_parallel_workers_per_gather  | 4   |
/var/lib/pgsql/11/data/postgresql.sumup.conf

Also session level on index restore there is
set max_parallel_maintenance_workers = 6;

Still we get only 2 parallel processes in a free of any other load system.
It is postgres 11.7

Where to look at ?

Thank You very much

Rado

psql \copy

[Steve Clark]

Hello,

I am using psql to copy data extracted from an InfluxDB in csv format into 
postgresql.
I have a key field on the time field which I have defined as a bigint since the 
time I get
from InfluxDB is an epoch time.

My question is does psql abort the copy if it hits a duplicate key, or does it 
keep processing?


Thanks,
--
Stephen Clark
NetWolves Managed Services, LLC.
Sr. Applications Architect
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com

Email Confidentiality Notice: The information contained in this transmission 
may contain privileged and confidential and/or protected health information 
(PHI) and may be subject to protection under the law, including the Health 
Insurance Portability and Accountability Act of 1996, as amended (HIPAA). This 
transmission is intended for the sole use of the individual or entity to whom 
it is addressed. If you are not the intended recipient, you are notified that 
any use, dissemination, distribution, printing or copying of this transmission 
is strictly prohibited and may subject you to criminal or civil penalties. If 
you have received this transmission in error, please contact the sender 
immediately and delete this email and any attachments from any computer. Vaso 
Corporation and its subsidiary companies are not responsible for data leaks 
that result from email messages received that contain privileged and 
confidential and/or protected health information (PHI).

Re: psql \copy

[Steve Crawford]

On Fri, Apr 24, 2020 at 8:55 AM Steve Clark 
wrote:

> Hello,
>
> I am using psql to copy data extracted from an InfluxDB in csv format into
> postgresql.
> I have a key field on the time field which I have defined as a bigint
> since the time I get
> from InfluxDB is an epoch time.
>
> My question is does psql abort the copy if it hits a duplicate key, or
> does it keep processing?
>
>
> The copy will fail. You could import into a temporary table and preprocess
then copy to your permanent table or use an ETL solution to remove unwanted
data before importing. I don't know the nature of your data or project but
perhaps that column isn't suitable for a key.

Cheers,
Steve

Re: psql \copy

[Adrian Klaver]

On 4/24/20 8:55 AM, Steve Clark wrote:

Hello,

I am using psql to copy data extracted from an InfluxDB in csv format 
into postgresql.
I have a key field on the time field which I have defined as a bigint 
since the time I get

from InfluxDB is an epoch time.

My question is does psql abort the copy if it hits a duplicate key, or 
does it keep processing?


Aborts.

\copy uses COPY so:

https://www.postgresql.org/docs/12/sql-copy.html

"COPY stops operation at the first error. This should not lead to 
problems in the event of a COPY TO, but the target table will already 
have received earlier rows in a COPY FROM. These rows will not be 
visible or accessible, but they still occupy disk space. This might 
amount to a considerable amount of wasted disk space if the failure 
happened well into a large copy operation. You might wish to invoke 
VACUUM to recover the wasted space."





Thanks,
--
Stephen Clark
*NetWolves Managed Services, LLC.*
Sr. Applications Architect
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com

Email Confidentiality Notice: The information contained in this 
transmission may contain privileged and confidential and/or protected 
health information (PHI) and may be subject to protection under the law, 
including the Health Insurance Portability and Accountability Act of 
1996, as amended (HIPAA). This transmission is intended for the sole use 
of the individual or entity to whom it is addressed. If you are not the 
intended recipient, you are notified that any use, dissemination, 
distribution, printing or copying of this transmission is strictly 
prohibited and may subject you to criminal or civil penalties. If you 
have received this transmission in error, please contact the sender 
immediately and delete this email and any attachments from any computer. 
Vaso Corporation and its subsidiary companies are not responsible for 
data leaks that result from email messages received that contain 
privileged and confidential and/or protected health information (PHI).



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

Re: psql \copy

[David G. Johnston]

On Fri, Apr 24, 2020 at 8:55 AM Steve Clark 
wrote:

> Hello,
>
> I am using psql to copy data extracted from an InfluxDB in csv format into
> postgresql.
> I have a key field on the time field which I have defined as a bigint
> since the time I get
> from InfluxDB is an epoch time.
>
> My question is does psql abort the copy if it hits a duplicate key, or
> does it keep processing?
>
>
Aborts

Re: psql \copy

[Steve Clark]

On 04/24/2020 11:59 AM, Steve Crawford wrote:
On Fri, Apr 24, 2020 at 8:55 AM Steve Clark 
mailto:steve.cl...@netwolves.com>> wrote:
Hello,

I am using psql to copy data extracted from an InfluxDB in csv format into 
postgresql.
I have a key field on the time field which I have defined as a bigint since the 
time I get
from InfluxDB is an epoch time.

My question is does psql abort the copy if it hits a duplicate key, or does it 
keep processing?


The copy will fail. You could import into a temporary table and preprocess then 
copy to your permanent table or use an ETL solution to remove unwanted data 
before importing. I don't know the nature of your data or project but perhaps 
that column isn't suitable for a key.

Cheers,
Steve
I am attempting to periodically pull time series data from an InfluxDB.
The column at issue is the timestamp. I have a script that pulls the last 15 
minutes of data from the InfluxDB
as csv data and pipe it into a psql -c "\copy" command. I was looking for 
the simplest way to do this.

--
Stephen Clark
NetWolves Managed Services, LLC.
Sr. Applications Architect
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com

Email Confidentiality Notice: The information contained in this transmission 
may contain privileged and confidential and/or protected health information 
(PHI) and may be subject to protection under the law, including the Health 
Insurance Portability and Accountability Act of 1996, as amended (HIPAA). This 
transmission is intended for the sole use of the individual or entity to whom 
it is addressed. If you are not the intended recipient, you are notified that 
any use, dissemination, distribution, printing or copying of this transmission 
is strictly prohibited and may subject you to criminal or civil penalties. If 
you have received this transmission in error, please contact the sender 
immediately and delete this email and any attachments from any computer. Vaso 
Corporation and its subsidiary companies are not responsible for data leaks 
that result from email messages received that contain privileged and 
confidential and/or protected health information (PHI).

Re: psql \copy

[Adrian Klaver]

On 4/24/20 9:12 AM, Steve Clark wrote:

On 04/24/2020 11:59 AM, Steve Crawford wrote:
On Fri, Apr 24, 2020 at 8:55 AM Steve Clark > wrote:


Hello,

I am using psql to copy data extracted from an InfluxDB in csv
format into postgresql.
I have a key field on the time field which I have defined as a
bigint since the time I get
from InfluxDB is an epoch time.

My question is does psql abort the copy if it hits a duplicate
key, or does it keep processing?


The copy will fail. You could import into a temporary table and 
preprocess then copy to your permanent table or use an ETL solution to 
remove unwanted data before importing. I don't know the nature of your 
data or project but perhaps that column isn't suitable for a key.


Cheers,
Steve

I am attempting to periodically pull time series data from an InfluxDB.
The column at issue is the timestamp. I have a script that pulls the 
last 15 minutes of data from the InfluxDB
as csv data and pipe it into a psql -c "\copy" command. I was 
looking for the simplest way to do this.


Then as suggested above pull into staging table that has no constraints 
e.g. PK. Verify data and then push into permanent table.




--
Stephen Clark
*NetWolves Managed Services, LLC.*
Sr. Applications Architect
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com



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

Re: psql \copy

[Rob Sargent]

On 4/24/20 10:12 AM, Steve Clark wrote:

On 04/24/2020 11:59 AM, Steve Crawford wrote:
On Fri, Apr 24, 2020 at 8:55 AM Steve Clark 
mailto:steve.cl...@netwolves.com>> wrote:


Hello,

I am using psql to copy data extracted from an InfluxDB in csv
format into postgresql.
I have a key field on the time field which I have defined as a
bigint since the time I get
from InfluxDB is an epoch time.

My question is does psql abort the copy if it hits a duplicate
key, or does it keep processing?


The copy will fail. You could import into a temporary table and 
preprocess then copy to your permanent table or use an ETL solution 
to remove unwanted data before importing. I don't know the nature of 
your data or project but perhaps that column isn't suitable for a key.


Cheers,
Steve

I am attempting to periodically pull time series data from an InfluxDB.
The column at issue is the timestamp. I have a script that pulls the 
last 15 minutes of data from the InfluxDB
as csv data and pipe it into a psql -c "\copy" command. I was 
looking for the simplest way to do this.


Is the duplication due to overlapping 15min chunks (i.e. imprecise 
definition of  "15 minutes ago")?  Perhaps retaining last timestamp sent 
to pg and use in the get-from-influx call?



--
Stephen Clark
*NetWolves Managed Services, LLC.*
Sr. Applications Architect
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com

Email Confidentiality Notice: The information contained in this 
transmission may contain privileged and confidential and/or protected 
health information (PHI) and may be subject to protection under the 
law, including the Health Insurance Portability and Accountability Act 
of 1996, as amended (HIPAA). This transmission is intended for the 
sole use of the individual or entity to whom it is addressed. If you 
are not the intended recipient, you are notified that any use, 
dissemination, distribution, printing or copying of this transmission 
is strictly prohibited and may subject you to criminal or civil 
penalties. If you have received this transmission in error, please 
contact the sender immediately and delete this email and any 
attachments from any computer. Vaso Corporation and its subsidiary 
companies are not responsible for data leaks that result from email 
messages received that contain privileged and confidential and/or 
protected health information (PHI). 

Re: psql \copy

[Steve Clark]

On 04/24/2020 12:15 PM, Adrian Klaver wrote:

On 4/24/20 9:12 AM, Steve Clark wrote:


On 04/24/2020 11:59 AM, Steve Crawford wrote:


On Fri, Apr 24, 2020 at 8:55 AM Steve Clark 
mailto:steve.cl...@netwolves.com>
> wrote:

   Hello,

   I am using psql to copy data extracted from an InfluxDB in csv
   format into postgresql.
   I have a key field on the time field which I have defined as a
   bigint since the time I get
   from InfluxDB is an epoch time.

   My question is does psql abort the copy if it hits a duplicate
   key, or does it keep processing?


The copy will fail. You could import into a temporary table and
preprocess then copy to your permanent table or use an ETL solution to
remove unwanted data before importing. I don't know the nature of your
data or project but perhaps that column isn't suitable for a key.

Cheers,
Steve


I am attempting to periodically pull time series data from an InfluxDB.
The column at issue is the timestamp. I have a script that pulls the
last 15 minutes of data from the InfluxDB
as csv data and pipe it into a psql -c "\copy" command. I was
looking for the simplest way to do this.



Then as suggested above pull into staging table that has no constraints
e.g. PK. Verify data and then push into permanent table.


Thanks for the tip. I'll head down that road. Stay safe everyone.






--
Stephen Clark
*NetWolves Managed Services, LLC.*
Sr. Applications Architect
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com







--
Stephen Clark
NetWolves Managed Services, LLC.
Sr. Applications Architect
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve.cl...@netwolves.com
http://www.netwolves.com

Email Confidentiality Notice: The information contained in this transmission 
may contain privileged and confidential and/or protected health information 
(PHI) and may be subject to protection under the law, including the Health 
Insurance Portability and Accountability Act of 1996, as amended (HIPAA). This 
transmission is intended for the sole use of the individual or entity to whom 
it is addressed. If you are not the intended recipient, you are notified that 
any use, dissemination, distribution, printing or copying of this transmission 
is strictly prohibited and may subject you to criminal or civil penalties. If 
you have received this transmission in error, please contact the sender 
immediately and delete this email and any attachments from any computer. Vaso 
Corporation and its subsidiary companies are not responsible for data leaks 
that result from email messages received that contain privileged and 
confidential and/or protected health information (PHI).

Re: psql \copy

[Ron]

You might want to investigate pg_bulkload for this activity.

On 4/24/20 10:55 AM, Steve Clark wrote:

Hello,

I am using psql to copy data extracted from an InfluxDB in csv format into 
postgresql.
I have a key field on the time field which I have defined as a bigint 
since the time I get

from InfluxDB is an epoch time.

My question is does psql abort the copy if it hits a duplicate key, or 
does it keep processing?




--
Angular momentum makes the world go 'round.

Re: create index insist on 2 workers only

[Peter Geoghegan]

On Fri, Apr 24, 2020 at 7:32 AM Radoslav Nedyalkov  wrote:
> We 're rebuilding a big table which has set parallel_workers = 6
> system has
>  max_parallel_maintenance_workers | 6   | 
> /var/lib/pgsql/11/data/postgresql.sumup.conf
>  max_parallel_workers | 16  | 
> /var/lib/pgsql/11/data/postgresql.sumup.conf
>  max_parallel_workers_per_gather  | 4   | 
> /var/lib/pgsql/11/data/postgresql.sumup.conf
>
> Also session level on index restore there is
> set max_parallel_maintenance_workers = 6;
>
> Still we get only 2 parallel processes in a free of any other load system.
> It is postgres 11.7

Try increasing maintenance_work_mem from the default of 64MB. MWM
constrains the number of parallel workers used.

-- 
Peter Geoghegan