Re: pg_rewind success even though getting error 'record with incorrect prev-link'

[Abdullah Al Maruf]

Hi Michael

> This is pointing out to the end of WAL for the current timeline.  You
> may face it after reading a WAL segment in an area which has been used
> in the past for a recycled segment.

Are you talking about error ` LOG:  invalid record length at 0/B98:
wanted 24, got 0` ?
or,
 `LOG:  record with incorrect prev-link 1/21B at 0/B98`

Actually, the 1st error is not making any issue. This node starts to
streaming from primary successfully.
But when the second error comes, It appears every 5 seconds. And, the node
is not streaming from master.

pg_rewind still resolves timeline conflict, but it's not fixing this second
error.

Any work around??

My scenario, in short, I have 1 master nodes (0th node) and three standby
nodes (1st,

2nd & 3rd node). When I make the 3rd node as master (by trigger file) and
restarts 0th node as a replica, It shows no problem.

But when both nodes are offline and our leader selection chooses the 0th
node
as a master, and tries to reattach the 3rd node as Replica, It throws an
error similar to:

```
LOG: invalid record length at 0/B98: wanted 24, got 0
LOG: started streaming WAL from primary at 0/B00 on timeline 2
LOG: record with incorrect prev-link 1/21B at 0/B98
FATAL: terminating walreceiver process due to administrator command
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
LOG: record with incorrect prev-link 1/21B at 0/B98
```

If I disable archive_mode, I never faced this error with the same script. It
only appears when archive is on, and also not all the times it happens but
most of the time it does.
The error message appears after every 5 seconds.

Scenario In details:

I have two folders for scripts.

├── primary
│ ├── postgresql.conf
│ ├── restore.sh
│ ├── run.sh
│ └── start.sh
└── replica
├── recovery.conf
└── run.sh

I have a system that will choose the leader. If the current pod is the
leader,
it will run `primary/run.sh`, and If it is a replica, it will run
`replica/run.sh`.

The files can be found here.

https://github.com/kubedb/postgres/tree/pg-db/hack/docker/postgres/9.6.7/scripts

On Wed, Jan 30, 2019 at 7:44 AM Michael Paquier  wrote:

> On Tue, Jan 29, 2019 at 07:13:11PM +0600, Abdullah Al Maruf wrote:
> > When I try to attach an old master with 'archiving set to on` as a new
> > standby, `pg_rewind` doesn't throw any error, But, when the database
> > starts, The following error appears:
> >
> > ```
> > LOG:  invalid record length at 0/B98: wanted 24, got 0
> > LOG:  started streaming WAL from primary at 0/B00 on timeline 2
> > LOG:  record with incorrect prev-link 1/21B at 0/B98
> > FATAL:  terminating walreceiver process due to administrator command
>
> This is pointing out to the end of WAL for the current timeline.  You
> may face it after reading a WAL segment in an area which has been used
> in the past for a recycled segment.
> --
> Michael
>

Re: Querying w/ join slow for large/many child tables

[Adrian Klaver]

On 1/29/19 6:49 PM, Wells Oliver wrote:
I have a primary parent table with a child table per week of the year 
for each week back through 2015. There are a lot of child tables. Each 
week's child table has maybe  80-110m rows.


When I join to the parent table on a column, it's very slow, but when I 
manually specify the specific week's child table, it's quite fast, e.g.


Slow:
select * from foo
join schema.mytable on foo.col = mytable.col

vs. fast:
select * from foo
join schema.mytable_2015_wk33 as mytable on foo.col = mytable.col

What's the resolution to make querying the table faster in terms of it 
finding the appropriate child table? Is it putting an index on `col` on 
each child table? Some other thing?


https://wiki.postgresql.org/wiki/Slow_Query_Questions



Thank you.

--
Wells Oliver
wells.oli...@gmail.com 



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

Querying w/ join slow for large/many child tables

[Wells Oliver]

I have a primary parent table with a child table per week of the year for
each week back through 2015. There are a lot of child tables. Each week's
child table has maybe  80-110m rows.

When I join to the parent table on a column, it's very slow, but when I
manually specify the specific week's child table, it's quite fast, e.g.

Slow:
select * from foo
join schema.mytable on foo.col = mytable.col

vs. fast:
select * from foo
join schema.mytable_2015_wk33 as mytable on foo.col = mytable.col

What's the resolution to make querying the table faster in terms of it
finding the appropriate child table? Is it putting an index on `col` on
each child table? Some other thing?

Thank you.

-- 
Wells Oliver
wells.oli...@gmail.com 

Re: pg_rewind success even though getting error 'record with incorrect prev-link'

[Michael Paquier]

On Tue, Jan 29, 2019 at 07:13:11PM +0600, Abdullah Al Maruf wrote:
> When I try to attach an old master with 'archiving set to on` as a new
> standby, `pg_rewind` doesn't throw any error, But, when the database
> starts, The following error appears:
> 
> ```
> LOG:  invalid record length at 0/B98: wanted 24, got 0
> LOG:  started streaming WAL from primary at 0/B00 on timeline 2
> LOG:  record with incorrect prev-link 1/21B at 0/B98
> FATAL:  terminating walreceiver process due to administrator command

This is pointing out to the end of WAL for the current timeline.  You
may face it after reading a WAL segment in an area which has been used
in the past for a recycled segment.
--
Michael


signature.asc
Description: PGP signature

Re: postgresql v11.1 Segmentation fault: signal 11: by running SELECT... JIT Issue?

[pabloa98]

I checked the table. It has 1265 columns. Sorry about the typo.

Pablo

On Tue, Jan 29, 2019 at 1:10 AM Andrew Gierth 
wrote:

> > "pabloa98" == pabloa98   writes:
>
>  pabloa98> I did not modify it.
>
> Then how did you create a table with more than 1600 columns? If I try
> and create a table with 1765 columns, I get:
>
> ERROR:  tables can have at most 1600 columns
>
> --
> Andrew (irc:RhodiumToad)
>

Re: Anonymize Data

[Sathish Kumar]

Hi Adrian,

I am looking to do it either during export or while importing data in the
secondary db.

On Tue, Jan 29, 2019, 10:43 PM Adrian Klaver  On 1/29/19 2:08 AM, Sathish Kumar wrote:
> > Hi Team,
> >
> > I am trying to protect some data on few tables when exporting to other
> > environments, is there anyway or extension which  can  anonymize of
> > personal data like name, credit card numbers ,etc. after import.
>
> Would you not want to anonymize before or during export?
>
> >
> > Thanks & Regards
> > Sathish Kumar.V
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: User Name Maps seem broken in 11.1 on CentOS 7

[Nick B]

Hey!

I think I've figured out what was your problem.

You have created a mapping to allow OS user "foobar" auth as pg role
"postgres".

What happens though (and error message actually indicates that) is you are
trying to authenticate as pg role "foobar". This is probably due to you
executing `psql` in terminal without specifying an actual user name.
The way to do this properly would be to execute `psql -U postgres`.

Unfortunately, you've left before I was able to tell you this.

Kind regards,
Nick.

Re: User Name Maps seem broken in 11.1 on CentOS 7

[Tom Lane]

Adrian Klaver  writes:
> On 1/29/19 1:11 PM, Viktor Berke wrote:
>> And here comes the problem: user name maps seem completely 
>> non-functional.

> Is ident_file set to something else?:
> https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-IDENT-FILE

Also: have you been reloading the server configuration after
modifying the file?  The postmaster only re-reads that file
after getting SIGHUP.  ("pg_ctl reload" is the usual way to
send the signal.)

regards, tom lane

Re: Anonymize Data

[Bruno Lavoie]

Not an extension but this post can be useful about various strategies:

https://blog.taadeem.net///english/2019/01/03/8_anonymization_strategies_with_postgres

Each with strengths and weaknesses

Le mar. 29 janv. 2019 5:08 AM, Sathish Kumar  a écrit :

> Hi Team,
>
> I am trying to protect some data on few tables when exporting to other
> environments, is there anyway or extension which  can  anonymize of
> personal data like name, credit card numbers ,etc. after import.
>
> Thanks & Regards
> Sathish Kumar.V
>

Re: How to set parameters in 'options'?

[Adrian Klaver]

On 1/29/19 10:03 AM, Stefan Keller wrote:

Many thanks to Igor and Adrian for your hints.

Got it to work like this:

create function link_server(_server text, _host text, _port text, _dbname text)
returns void as $$
begin
   execute format('create server %s
 foreign data wrapper postgres_fdw
 options (host %L, port %L, dbname %L)', _server, _host, _port, _dbname);
end;
$$ language plpgsql;


I would go with %I instead of %s for _server.



select link_server('other_db_server', '111.11.11.11', '5432', 'other_db');

But I actually hoped being able avoid "execute"...


Why?



:Stefan

Am Di., 29. Jan. 2019 um 16:21 Uhr schrieb Adrian Klaver
:


On 1/29/19 6:40 AM, Stefan Keller wrote:

Hi,

I'd like to write a function like this:

create function foo(_host text, _port text, _dbname text)
returns void as $$
create server _server
  foreign data wrapper postgres_fdw
  options (host _host, port _port, dbname _dbname);
  -- ERROR: syntax error at or near "_host"
$$ language sql;

In order to e.g. do:
select foo('111.11.11.11', '5432', 'mydb');

How can I set the parameters in 'options' using those variables?


If you are going to use plpgsql, then format:

https://www.postgresql.org/docs/10/functions-string.html#FUNCTIONS-STRING-FORMAT

https://www.postgresql.org/docs/10/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE

So something like:

execute format('create server _server
  foreign data wrapper postgres_fdw
  options (host $1, port $2, dbname $3') USING _host, _port, _dbname

Not sure where the _server is coming from so that might have to be dealt
with.



:Stefan

P.S. Actually I'll put this code in a plggsql function later on.





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





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

Re: User Name Maps seem broken in 11.1 on CentOS 7

[Adrian Klaver]

On 1/29/19 1:11 PM, Viktor Berke wrote:

Hi,

After some talk with the helpful folks of #postgresql I see no other 
option but to ask here. I'm trying to set up proper authentication for 
our corprorate users. They'll access postgres both from their 
workstations via TCP, and also locally. Locally, they're authenticated 
using SSSD which in turn is using LDAP to talk to our Active Directory 
DCs. That's not very relevant, but I just wanted to explain precisely.


Anyhow, we try to enforce the "user.n...@company.com" login wherever we 
can, so this is how I set up LDAP auth:


hostssl all all 10.1.0.1/16 ldap ldapserver=dc2.ad.foobar.com 
ldapport=636 ldapscheme=ldaps ldaptls=0 
ldapbinddn="CN=ldap,OU=Helpers,OU=Foobar,DC=ad,DC=foobar,DC=com" 
ldapbindpasswd=*** ldapsearchattribute=mail 
ldapbasedn="OU=Users,OU=Foobar,DC=ad,DC=foobar,DC=com"


This works perfectly fine. I create the role, e.g.:

CREATE ROLE "jane@foobar.com" CREATEDB CREATEROLE LOGIN;

Then she can log in fine via pgAdmin or whatever, using her email address.

Now I want to set up peer authentication locally, so that they don't 
have to enter their passwords all the time when they're already 
authenticated to the OS. The idea is that I map the local "jane.doe" OS 
user to the "jane@foobar.com" role already present in postgres. This 
way I don't have to CREATE ROLE and manage permissions both for jane.doe 
and jane@foobar.com. So the map would look something like this, I guess:


foo /^(.*)$ \1@foobar\.com (or something like that?)

And here comes the problem: user name maps seem completely 
non-functional. First I suspected it's a problem with the dot in 
usernames, but even if I create a local Unix user ("foobar") and set


local all all peer map=foo

in pg_hba.conf and

foo foobar postgres

In pg_ident.conf, all I see in the log is that

2019-01-29 21:44:45.095 CET [41929] LOG:  no match in usermap "foo" for 
user "foobar" authenticated as "foobar"
2019-01-29 21:44:45.095 CET [41929] FATAL:  Peer authentication failed 
for user "foobar"
2019-01-29 21:44:45.095 CET [41929] DETAIL:  Connection matched 
pg_hba.conf line 79: "local all all peer map=foo"


Bummer. I also tried various regexes, even the likes of /^(.*)$, but the 
log ALWAYS says no match. The weird thing is that this is the log 
content even if there's nothing in pg_ident.conf, so it's like postgres 
doesn't even care about what's in there.


Is ident_file set to something else?:

https://www.postgresql.org/docs/11/runtime-config-file-locations.html#GUC-IDENT-FILE



Any ideas?

Regards,

Viktor



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

User Name Maps seem broken in 11.1 on CentOS 7

[Viktor Berke]

Hi,

After some talk with the helpful folks of #postgresql I see no other option but 
to ask here. I'm trying to set up proper authentication for our corprorate 
users. They'll access postgres both from their workstations via TCP, and also 
locally. Locally, they're authenticated using SSSD which in turn is using LDAP 
to talk to our Active Directory DCs. That's not very relevant, but I just 
wanted to explain precisely.

Anyhow, we try to enforce the "user.n...@company.com" login wherever we can, so 
this is how I set up LDAP auth:

hostssl all all 10.1.0.1/16 ldap ldapserver=dc2.ad.foobar.com ldapport=636 
ldapscheme=ldaps ldaptls=0 
ldapbinddn="CN=ldap,OU=Helpers,OU=Foobar,DC=ad,DC=foobar,DC=com" 
ldapbindpasswd=*** ldapsearchattribute=mail 
ldapbasedn="OU=Users,OU=Foobar,DC=ad,DC=foobar,DC=com"

This works perfectly fine. I create the role, e.g.:

CREATE ROLE "jane@foobar.com" CREATEDB CREATEROLE LOGIN;

Then she can log in fine via pgAdmin or whatever, using her email address.

Now I want to set up peer authentication locally, so that they don't have to 
enter their passwords all the time when they're already authenticated to the 
OS. The idea is that I map the local "jane.doe" OS user to the 
"jane@foobar.com" role already present in postgres. This way I don't have 
to CREATE ROLE and manage permissions both for jane.doe and 
jane@foobar.com. So the map would look something like this, I guess:

foo /^(.*)$ \1@foobar\.com (or something like that?)

And here comes the problem: user name maps seem completely non-functional. 
First I suspected it's a problem with the dot in usernames, but even if I 
create a local Unix user ("foobar") and set

local all all peer map=foo

in pg_hba.conf and

foo foobar postgres

In pg_ident.conf, all I see in the log is that

2019-01-29 21:44:45.095 CET [41929] LOG:  no match in usermap "foo" for user 
"foobar" authenticated as "foobar"
2019-01-29 21:44:45.095 CET [41929] FATAL:  Peer authentication failed for user 
"foobar"
2019-01-29 21:44:45.095 CET [41929] DETAIL:  Connection matched pg_hba.conf 
line 79: "local all all peer map=foo"

Bummer. I also tried various regexes, even the likes of /^(.*)$, but the log 
ALWAYS says no match. The weird thing is that this is the log content even if 
there's nothing in pg_ident.conf, so it's like postgres doesn't even care about 
what's in there.

Any ideas?

Regards,

Viktor

Re: Revoke SQL doesn't take effect

[Tim Cross]

On Wed, 30 Jan 2019 at 07:49, Jason W  wrote:

> I have two postgresql accounts created by someone else who I do not know
> (So I do not know setting for those accounts and tables created). One is
> read only account e.g. read_only_user (This can perform select operations
> only). The other is admin account e.g. admin_user (This can perform grant,
> revoke, CRUD,  and so on operations).
>
> The read only account can query (select  sql) a table (suppose it's called
> table1) under a specific schema (suppose it's schema1). For instance select
> * from schema1.table1. Now I received a request to revoke select for that
> read only account on table1. So I execute
>
> revoke select on schema1.table1 from read_only_user
>
> psql returns REVOKE string (or something similar showing the sql execution
> was successful) on console. However, when check with read_only_user
> account. I am still able to query table1. Searching the internet, [1] looks
> like the closest to my problem. But I do not find solution in that thread.
>
> So my question:
> What steps do I need to perform in order to exactly revoke select from
> read only user account for a particular table? So the read only user
> account wont' be able query that specific table with select permission
> revoke (psql should returns info like permission denied).
>
> Thanks
>
> [1].
> https://www.postgresql.org/message-id/00ce01cbef04%24910606b0%24b3121410%24%40yahoo.com
>
>
It is likely that permissions for the user are being granted via a role
rather than granted directly to the user (think of a role as a user account
which does not have the login permission). First thing to check would be to
look at what roles have been granted to the read_only user and if one of
those grants select on schema1.table1, revoke/remove it from the role.
There may be other complications, such as roles which do a grant select on
all tables in a schema, so getting the order of things correct is
important. First step, understanding how permissions are granted, then you
should be able to revoke them effectively.

Tim

-- 
regards,

Tim

--
Tim Cross

Revoke SQL doesn't take effect

[Jason W]

I have two postgresql accounts created by someone else who I do not know (So I 
do not know setting for those accounts and tables created). One is read only 
account e.g. read_only_user (This can perform select operations only). The 
other is admin account e.g. admin_user (This can perform grant, revoke, CRUD,  
and so on operations).

The read only account can query (select  sql) a table (suppose it's called 
table1) under a specific schema (suppose it's schema1). For instance select * 
from schema1.table1. Now I received a request to revoke select for that read 
only account on table1. So I execute

revoke select on schema1.table1 from read_only_user

psql returns REVOKE string (or something similar showing the sql execution was 
successful) on console. However, when check with read_only_user account. I am 
still able to query table1. Searching the internet, [1] looks like the closest 
to my problem. But I do not find solution in that thread.

So my question:
What steps do I need to perform in order to exactly revoke select from read 
only user account for a particular table? So the read only user account wont' 
be able query that specific table with select permission revoke (psql should 
returns info like permission denied).

Thanks

[1]. 
https://www.postgresql.org/message-id/00ce01cbef04%24910606b0%24b3121410%24%40yahoo.com

Re: Table Replication

[Rene Romero Benavides]

You could also use Slony-I

On Tue, Jan 29, 2019 at 12:24 PM Thomas Endres 
wrote:

>
> On Jan 29, 2019, at 8:22 AM, Fabrízio de Royes Mello <
> fabri...@timbira.com.br> wrote:
>
>
>
> Em ter, 29 de jan de 2019 às 10:24, Sathish Kumar 
> escreveu:
> >
> > Hi,
> >
> > We are trying to replicate few tables from one postgresql server to
> another server. We are currently using Postgresql 9.5.x, is there any way
> to achieve it without Postgresql upgrade.
>
> Yeap... use pglogical [1] extension.
>
>
> I have had success with pgloader when migrating specific tables and/or
> schemas from one machine to another.
> 
>
>
> Best,
> Tom Endres
>


-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: type int2vector

[Rene Romero Benavides]

What's the question?

On Mon, Jan 28, 2019 at 4:25 AM 吉成恒  wrote:

>
> select * from pg_partition where 2 =all(pg_partition.paratts);
>
> --
>
> 吉成恒
>
> 光大证券股份有限公司 信息技术总部（数据中心）
>
> 地址：静安区新闸路1508号7楼
>
> 电话：021-22169287
>
> 手机：18717772189
>
> 邮箱：j...@ebscn.com
>
>

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: Table Replication

[Thomas Endres]

> On Jan 29, 2019, at 8:22 AM, Fabrízio de Royes Mello 
>  wrote:
> 
> 
> 
> Em ter, 29 de jan de 2019 às 10:24, Sathish Kumar  > escreveu:
> >
> > Hi,
> >
> > We are trying to replicate few tables from one postgresql server to another 
> > server. We are currently using Postgresql 9.5.x, is there any way to 
> > achieve it without Postgresql upgrade.
> 
> Yeap... use pglogical [1] extension.

I have had success with pgloader when migrating specific tables and/or schemas 
from one machine to another.
>


Best,
Tom Endres

Re: How to set parameters in 'options'?

[Stefan Keller]

Many thanks to Igor and Adrian for your hints.

Got it to work like this:

create function link_server(_server text, _host text, _port text, _dbname text)
returns void as $$
begin
  execute format('create server %s
foreign data wrapper postgres_fdw
options (host %L, port %L, dbname %L)', _server, _host, _port, _dbname);
end;
$$ language plpgsql;

select link_server('other_db_server', '111.11.11.11', '5432', 'other_db');

But I actually hoped being able avoid "execute"...

:Stefan

Am Di., 29. Jan. 2019 um 16:21 Uhr schrieb Adrian Klaver
:
>
> On 1/29/19 6:40 AM, Stefan Keller wrote:
> > Hi,
> >
> > I'd like to write a function like this:
> >
> > create function foo(_host text, _port text, _dbname text)
> > returns void as $$
> >create server _server
> >  foreign data wrapper postgres_fdw
> >  options (host _host, port _port, dbname _dbname);
> >  -- ERROR: syntax error at or near "_host"
> > $$ language sql;
> >
> > In order to e.g. do:
> > select foo('111.11.11.11', '5432', 'mydb');
> >
> > How can I set the parameters in 'options' using those variables?
>
> If you are going to use plpgsql, then format:
>
> https://www.postgresql.org/docs/10/functions-string.html#FUNCTIONS-STRING-FORMAT
>
> https://www.postgresql.org/docs/10/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE
>
> So something like:
>
> execute format('create server _server
>  foreign data wrapper postgres_fdw
>  options (host $1, port $2, dbname $3') USING _host, _port, _dbname
>
> Not sure where the _server is coming from so that might have to be dealt
> with.
>
> >
> > :Stefan
> >
> > P.S. Actually I'll put this code in a plggsql function later on.
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com

parallel plpgsql function

[Jiří Fejfar]

Hi all!

I am trying to create basic experiment [1] analysing some data with plpgsql
function in parallel.

I am able to aggregate data in parallel (6 workers) with pure SQL code
(line 19) but as far as I put the same query (with some parametrization)
into plpgsql function and run it via RETURN QUERY EXECUTE it doesn't run in
parallel anymore (line 69). I would understand, that planner doesn't see
into dynamic SQL being executed here or something like this.

But what is more surprising to me is, when I am trying to call that
function 10x with different settings (line 84). It is also running on
single CPU (probably according to 10x longer execution time) although the
function is marked as PARALLEL SAFE.

I hope that at least the second example can be parallelized without any
"conceptual" problems. Do I miss something?

And one last question: is it possible to see query plan which is created
"inside" function call when query is actually executed?

Thank you in advance, Jiří.

[1] https://gitlab.com/snippets/1806432

Re: SELECT of pseudo hex value gives unexpected result

[Gunnar "Nick" Bluth]

Am 29.01.2019 um 17:39 schrieb Tom Lane:
> "Gunnar \"Nick\" Bluth"  writes:
>> Tried
>> SELECT 0x5e73266725;

[...]

> SELECT 0 AS x5e73266725;
> 
> and that's the result you got.

Well, yeah, _that_ was pretty obvious. I just didn't expect ot to happen...

> I think that the SQL standard considers adjacent tokens to be invalid
> unless one of them is punctuation (e.g. 1+2), but our lexer is a bit
> less rigid about that.

it kind of comforts me that it's at least not defined like that in the
standard ;-)

Cheers anyway!
-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bl...@pro-open.de
__
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne



signature.asc
Description: OpenPGP digital signature

Re: SELECT of pseudo hex value gives unexpected result

[Laurenz Albe]

Gunnar "Nick" Bluth wrote:
> Tried
> SELECT 0x5e73266725;
> 
> and received:
> -[ RECORD 1 ]--
> x5e73266725 | 0
> 
> That was not what I expected... is this expected/documented behaviour?

Looks like you don't need a space between a number literal and
the column alias.

I don't see any problem with that.

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

Re: SELECT of pseudo hex value gives unexpected result

[Tom Lane]

"Gunnar \"Nick\" Bluth"  writes:
> Tried
> SELECT 0x5e73266725;

> and received:
> -[ RECORD 1 ]--
> x5e73266725 | 0

> That was not what I expected... is this expected/documented behaviour?

Well, there are no hex literals in (PG's notion of) SQL, so that isn't
a valid token.  But it's the concatenation of two valid tokens.  So
what you wrote is the same as

SELECT 0 x5e73266725;

which is an abbreviation for

SELECT 0 AS x5e73266725;

and that's the result you got.

I think that the SQL standard considers adjacent tokens to be invalid
unless one of them is punctuation (e.g. 1+2), but our lexer is a bit
less rigid about that.

regards, tom lane

SELECT of pseudo hex value gives unexpected result

[Gunnar "Nick" Bluth]

Hi,

I found this in an SQL-injection attempt today:
union select 0x5e73266725,0x5e73266725[,...],0x5e73266725;

Tried
SELECT 0x5e73266725;

and received:
-[ RECORD 1 ]--
x5e73266725 | 0

That was not what I expected... is this expected/documented behaviour?

Thx in advance!
-- 
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil   +49 172 8853339
Email: gunnar.bl...@pro-open.de
__
In 1984 mainstream users were choosing VMS over UNIX.  Ten years later
they are choosing Windows over UNIX.  What part of that message aren't you
getting? - Tom Payne



signature.asc
Description: OpenPGP digital signature

Re: How to set parameters in 'options'?

[Adrian Klaver]

On 1/29/19 6:40 AM, Stefan Keller wrote:

Hi,

I'd like to write a function like this:

create function foo(_host text, _port text, _dbname text)
returns void as $$
   create server _server
 foreign data wrapper postgres_fdw
 options (host _host, port _port, dbname _dbname);
 -- ERROR: syntax error at or near "_host"
$$ language sql;

In order to e.g. do:
select foo('111.11.11.11', '5432', 'mydb');

How can I set the parameters in 'options' using those variables?


If you are going to use plpgsql, then format:

https://www.postgresql.org/docs/10/functions-string.html#FUNCTIONS-STRING-FORMAT

https://www.postgresql.org/docs/10/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE

So something like:

execute format('create server _server
foreign data wrapper postgres_fdw
options (host $1, port $2, dbname $3') USING _host, _port, _dbname

Not sure where the _server is coming from so that might have to be dealt 
with.




:Stefan

P.S. Actually I'll put this code in a plggsql function later on.





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

RE: How to set parameters in 'options'?

[Igor Neyman]

-Original Message-
From: Stefan Keller [mailto:sfkel...@gmail.com] 
Sent: Tuesday, January 29, 2019 9:40 AM
To: Postgres General 
Subject: How to set parameters in 'options'?

Hi,

I'd like to write a function like this:

create function foo(_host text, _port text, _dbname text) returns void as $$
  create server _server
foreign data wrapper postgres_fdw
options (host _host, port _port, dbname _dbname);
-- ERROR: syntax error at or near "_host"
$$ language sql;

In order to e.g. do:
select foo('111.11.11.11', '5432', 'mydb');

How can I set the parameters in 'options' using those variables?

:Stefan

P.S. Actually I'll put this code in a plggsql function later on.
__

Use dynamic sql:
https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards,
Igor Neyman

Re: Anonymize Data

[Adrian Klaver]

On 1/29/19 2:08 AM, Sathish Kumar wrote:

Hi Team,

I am trying to protect some data on few tables when exporting to other 
environments, is there anyway or extension which  can  anonymize of  
personal data like name, credit card numbers ,etc. after import.


Would you not want to anonymize before or during export?



Thanks & Regards
Sathish Kumar.V



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

How to set parameters in 'options'?

[Stefan Keller]

Hi,

I'd like to write a function like this:

create function foo(_host text, _port text, _dbname text)
returns void as $$
  create server _server
foreign data wrapper postgres_fdw
options (host _host, port _port, dbname _dbname);
-- ERROR: syntax error at or near "_host"
$$ language sql;

In order to e.g. do:
select foo('111.11.11.11', '5432', 'mydb');

How can I set the parameters in 'options' using those variables?

:Stefan

P.S. Actually I'll put this code in a plggsql function later on.

Re: error when creating logical replication slot

[Josef Machytka]

Hi. Thanks for answer.

Both databases run on Google Compute Engine VMs with Debian 9, both PGs are
"PostgreSQL 11.1 (Debian 11.1-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit"

I did several tests with setting logical replication from master to replica
always dropping everything and setting it "from scratch" again with exactly
the same bash script running plpgsql commands in psql and only during one
of these tests got this error. And now I cannot repeat it.

Not sure if it can be important in this context but database processes
several thousands of inserts each minute, most of them just 1 record in
separate transaction - those are metric data from web widgets.

JM


On Mon, 28 Jan 2019 at 15:15, Tom Lane  wrote:

> Josef Machytka  writes:
> > I am implementing logical replication and today during some final tests
> > before implementing it to the production I have got this error when
> logical
> > replication slot on master was created.
>
> > NOTICE:  2019-01-28 09:08:22.081007+00: logical replication slot x
> does
> > not exist - creating...
> > ERROR:  snapshot reference 0x55a52a898340 is not owned by resource owner
> > TopTransaction
> > CONTEXT:  SQL statement "SELECT
> pg_create_logical_replication_slot('x',
> > 'pgoutput')"
>
> That's definitely a bug.  Are you on the latest minor release of whichever
> release series you're using?  (And which one is that?)  If it is latest,
> please see if you can make a self-contained test case that triggers this.
>
> regards, tom lane
>

Re: Table Replication

[Fabrízio de Royes Mello]

Em ter, 29 de jan de 2019 às 10:24, Sathish Kumar 
escreveu:
>
> Hi,
>
> We are trying to replicate few tables from one postgresql server to
another server. We are currently using Postgresql 9.5.x, is there any way
to achieve it without Postgresql upgrade.

Yeap... use pglogical [1] extension.

Regards,

[1] https://www.2ndquadrant.com/en/resources/pglogical/

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Help : Update and insert record based on several value in the parameter

[Tony Shelver]

I don't know what you are using as a front end to call this update, but I
have been finding json/jsonb parameters passed into a function / procedure
and then using postgres json functions to process the data as a good
solution.

If your data fits postgres array datatype, as others have mentioned, that
is also a good (and possibly simpler) solution.

On Tue, 29 Jan 2019 at 14:50, Hengky Lie  wrote:

> Hi,
>
> I want to create a function to update my table (flag='9') and insert new
> record (flag='0') with the rfidnumber specified in a parameter.
>
> This parameter may have several value seperated by space (ie. 11 22 33 44)
>
> CREATE OR REPLACE FUNCTION public.fcreate_rfid (
>   znumber varchar
> )
> RETURNS boolean AS
> $body$
> BEGIN
> --update old record which has the same rfid number and flag='0' if exists
> update tblrfid set flag='9' where flag='0' and rfidnumber in (znumber);
>
> -- generate new record
> insert into tblrfid(tanggal, flag, rfidnumber)
> select localtimestamp, '0', regexp_split_to_table(znumber, ' ');
>
>  return true;
> END;
> $body$
> LANGUAGE 'plpgsql';
>
> when i called this function using command :
>
> select fcreate_rfid('11 22 33 44');
>
> This function fail to update the old record, but success to insert the new
> record.
>
> Please help me how to fixed this problem. I know the problem is the update
> command, but i don't know the correct it. Googling anywhere didn't find any
> solution.
>
> Thank you
>
>
>
> 
>  Virus-free.
> www.avg.com
> 
> <#m_5923011125801819668_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

Re: Anonymize Data

[Sathish Kumar]

Hi,

Thanks, I have checked about this extension.
Is it reliable to use.

On Tue, Jan 29, 2019, 9:02 PM Tumasgiu Rossini  Hi,
>
> you should check this thread :
>
>
> https://www.postgresql.org/message-id/flat/CABokaaPSrK6%2BFFAdfT8gg-mNLXB9612NezaCbTNRtC7HqztkKw%40mail.gmail.com
>
> Le mar. 29 janv. 2019 à 11:08, Sathish Kumar  a
> écrit :
>
>> Hi Team,
>>
>> I am trying to protect some data on few tables when exporting to other
>> environments, is there anyway or extension which  can  anonymize of
>> personal data like name, credit card numbers ,etc. after import.
>>
>> Thanks & Regards
>> Sathish Kumar.V
>>
>

pg_rewind success even though getting error 'record with incorrect prev-link'

[Abdullah Al Maruf]

When I try to attach an old master with 'archiving set to on` as a new
standby, `pg_rewind` doesn't throw any error, But, when the database
starts, The following error appears:

```
LOG:  invalid record length at 0/B98: wanted 24, got 0
LOG:  started streaming WAL from primary at 0/B00 on timeline 2
LOG:  record with incorrect prev-link 1/21B at 0/B98
FATAL:  terminating walreceiver process due to administrator command
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
```

Re: Help : Update and insert record based on several value in the parameter

[Pavel Stehule]

út 29. 1. 2019 v 13:50 odesílatel Hengky Lie 
napsal:

> Hi,
>
> I want to create a function to update my table (flag='9') and insert new
> record (flag='0') with the rfidnumber specified in a parameter.
>
> This parameter may have several value seperated by space (ie. 11 22 33 44)
>

For this kind of parameters, PostgreSQL has a array type - for example a
int[] can be perfect for this purpose

If you still use a this format, then you can use a string_to_array function
and searching in array

so your UPDATE command should to look like

UPDATE tblrfid SET flag = 9 WHERE flag = '0' and rfidnumber =
ANY(string_to_array(znumber))

Regards

Pavel


> CREATE OR REPLACE FUNCTION public.fcreate_rfid (
>   znumber varchar
> )
> RETURNS boolean AS
> $body$
> BEGIN
> --update old record which has the same rfid number and flag='0' if exists
> update tblrfid set flag='9' where flag='0' and rfidnumber in (znumber);
>
> -- generate new record
> insert into tblrfid(tanggal, flag, rfidnumber)
> select localtimestamp, '0', regexp_split_to_table(znumber, ' ');
>
>  return true;
> END;
> $body$
> LANGUAGE 'plpgsql';
>
> when i called this function using command :
>
> select fcreate_rfid('11 22 33 44');
>
> This function fail to update the old record, but success to insert the new
> record.
>
> Please help me how to fixed this problem. I know the problem is the update
> command, but i don't know the correct it. Googling anywhere didn't find any
> solution.
>
> Thank you
>
>
>
> 
>  Virus-free.
> www.avg.com
> 
> <#m_-6825970932945715657_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>

Re: Help : Update and insert record based on several value in the parameter

[Andrew Gierth]

> "Hengky" == Hengky Lie  writes:

 Hengky> Hi,

 Hengky> I want to create a function to update my table (flag='9') and
 Hengky> insert new record (flag='0') with the rfidnumber specified in a
 Hengky> parameter.

rfidnumber is stored as text/varchar? if it's something else, change the
suggestions below accordingly.

 Hengky> This parameter may have several value seperated by space (ie.
 Hengky> 11 22 33 44)

update ... and rfidnumber = any (string_to_array(znumber,' '))

Or, do the split just once:

DECLARE
  z_ids text[] := string_to_array(znumber, ' ');
BEGIN
  update ... where flag='0' and rfidnumber =any (z_ids);
  insert into ...
  select localtimestamp, '0', id from unnest(z_ids) as u(id);

-- 
Andrew (irc:RhodiumToad)

Help : Update and insert record based on several value in the parameter

[Hengky Lie]

Hi,

I want to create a function to update my table (flag='9') and insert new
record (flag='0') with the rfidnumber specified in a parameter.

This parameter may have several value seperated by space (ie. 11 22 33 44)

CREATE OR REPLACE FUNCTION public.fcreate_rfid (
  znumber varchar
)
RETURNS boolean AS
$body$
BEGIN
--update old record which has the same rfid number and flag='0' if exists
update tblrfid set flag='9' where flag='0' and rfidnumber in (znumber);

-- generate new record
insert into tblrfid(tanggal, flag, rfidnumber)
select localtimestamp, '0', regexp_split_to_table(znumber, ' ');

 return true;
END;
$body$
LANGUAGE 'plpgsql';

when i called this function using command :

select fcreate_rfid('11 22 33 44');

This function fail to update the old record, but success to insert the new
record.

Please help me how to fixed this problem. I know the problem is the update
command, but i don't know the correct it. Googling anywhere didn't find any
solution.

Thank you



Virus-free.
www.avg.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Help : Update and insert record based on several value in the parameter

[Hengky Lie]

Hi,

I want to create a function to update my table (flag='9') and insert new
record (flag='0') with the rfidnumber specified in a parameter.

This parameter may have several value seperated by space (ie. 11 22 33 44)

CREATE OR REPLACE FUNCTION public.fcreate_rfid (
  znumber varchar
)
RETURNS boolean AS
$body$
BEGIN
--update old record which has the same rfid number and flag='0' if exists
update tblrfid set flag='9' where flag='0' and rfidnumber in (znumber);

-- generate new record
insert into tblrfid(tanggal, flag, rfidnumber)
select localtimestamp, '0', regexp_split_to_table(znumber, ' ');

 return true;
END;
$body$
LANGUAGE 'plpgsql';

when i called this function using command :

select fcreate_rfid('11 22 33 44');

This function fail to update the old record, but success to insert the new
record.

Please help me how to fixed this problem. I know the problem is the update
command, but i don't know the correct it. Googling anywhere didn't find a
solution.

Thank you


Virus-free.
www.avg.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Error: record with incorrect prev-link ---/--- at ---/---, when archiving is 'on'

[Abdullah Al Maruf]

Hello,
I am trying to build an automated system in docker/kubernetes where a
container/pod will automatically schedule itself as a Master or Standby.

In short, I have 1 master nodes (0th node) and three standby nodes (1st,
2nd & 3rd node). When I make the 3rd node as master (by trigger file) and
restarts 0th node as a replica, It shows no problem.

But when, both nodes are offline and our leader selection chooses 0th node
as a master, and tries to reattach the 3rd node as Replica, It throws an
error similar to:

```
LOG:  invalid record length at 0/B98: wanted 24, got 0
LOG:  started streaming WAL from primary at 0/B00 on timeline 2
LOG:  record with incorrect prev-link 1/21B at 0/B98
FATAL:  terminating walreceiver process due to administrator command
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
LOG:  record with incorrect prev-link 1/21B at 0/B98
```

If I disable archive_mode, I never faced this error with same script. It
only apperas when archive is on, and also not all the times it happens but
most of the time it does.
The error message appears after every 5 seconds.



Scenario In details:

I have two folders for scripts.

├── primary
│   ├── postgresql.conf
│   ├── restore.sh
│   ├── run.sh
│   └── start.sh
└── replica
├── recovery.conf
└── run.sh

I have a system that will choose the leader. If the current pod is leader,
it will run `primary/run.sh`, and If it is a replica, it will run
`replica/run.sh`. The problem is not related to restore.sh at this moment.
So I am skipping restore.sh.

The files can be found here.
https://github.com/kubedb/postgres/tree/master/hack/docker/postgres/9.6.7/scripts

Table Replication

[Sathish Kumar]

Hi,

We are trying to replicate few tables from one postgresql server to another
server. We are currently using Postgresql 9.5.x, is there any way to
achieve it without Postgresql upgrade.

Re: postgresql v11.1 Segmentation fault: signal 11: by running SELECT... JIT Issue?

[pabloa98]

I appreciate your advice. I will check the number of columns in that table.



On Tue, Jan 29, 2019, 1:53 AM Andrew Gierth 
wrote:

> > "pabloa98" == pabloa98   writes:
>
>  pabloa98> I found this article:
>
>  pabloa98>
> https://manual.limesurvey.org/Instructions_for_increasing_the_maximum_number_of_columns_in_PostgreSQL_on_Linux
>
> Those instructions contain obvious errors.
>
>  pabloa98> It seems I should modify: uint8 t_hoff;
>  pabloa98> and replace it with something like: uint32 t_hoff; or uint64
> t_hoff;
>
> At the very least, that ought to be uint16 t_hoff; since there is never
> any possibility of hoff being larger than 32k since that's the largest
> allowed pagesize. However, if you modify that, it's then up to you to
> ensure that all the code that assumes it's a uint8 is found and fixed.
> I have no idea what else would break.
>
> --
> Andrew (irc:RhodiumToad)
>

Anonymize Data

[Sathish Kumar]

Hi Team,

I am trying to protect some data on few tables when exporting to other
environments, is there anyway or extension which  can  anonymize of
personal data like name, credit card numbers ,etc. after import.

Thanks & Regards
Sathish Kumar.V

Re: postgresql v11.1 Segmentation fault: signal 11: by running SELECT... JIT Issue?

[Andrew Gierth]

> "pabloa98" == pabloa98   writes:

 pabloa98> I found this article:

 pabloa98> 
https://manual.limesurvey.org/Instructions_for_increasing_the_maximum_number_of_columns_in_PostgreSQL_on_Linux

Those instructions contain obvious errors.

 pabloa98> It seems I should modify: uint8 t_hoff;
 pabloa98> and replace it with something like: uint32 t_hoff; or uint64 t_hoff;

At the very least, that ought to be uint16 t_hoff; since there is never
any possibility of hoff being larger than 32k since that's the largest
allowed pagesize. However, if you modify that, it's then up to you to
ensure that all the code that assumes it's a uint8 is found and fixed.
I have no idea what else would break.

-- 
Andrew (irc:RhodiumToad)

Re: postgresql v11.1 Segmentation fault: signal 11: by running SELECT... JIT Issue?

[Andrew Gierth]

> "pabloa98" == pabloa98   writes:

 pabloa98> I did not modify it.

Then how did you create a table with more than 1600 columns? If I try
and create a table with 1765 columns, I get:

ERROR:  tables can have at most 1600 columns

-- 
Andrew (irc:RhodiumToad)

Re: postgresql v11.1 Segmentation fault: signal 11: by running SELECT... JIT Issue?

[pabloa98]

I found this article:

https://manual.limesurvey.org/Instructions_for_increasing_the_maximum_number_of_columns_in_PostgreSQL_on_Linux

It seems I should modify: uint8 t_hoff;
and replace it with something like: uint32 t_hoff; or uint64 t_hoff;

And perhaps should I modify this too?

The fix is easy enough, just adding a
v_hoff = LLVMBuildZExt(b, v_hoff, LLVMInt32Type(), "");
fixes the issue for me.
If that is the case, I am not sure what kind of modification we should do.


I feel I need to explain why we create these huge tables. Basically we want
to process big matrices for machine learning.
Using tables with classic columns let us write very clear code. If we have
to start using arrays as columns, things would become complicated and not
intuitive (besides, some columns store vectors as arrays... ).

We could use JSONB (we do, but for json documents). The problem is, storing
large amounts of jsonb columns create performance issues (compared with
normal tables).

Since almost everybody is doing ML to apply to different products, perhaps
are there other companies interested in a version of Postgres that could
deal with tables with thousands of columns?
I did not find any postgres package ready to use like that though.

Pablo




On Tue, Jan 29, 2019 at 12:11 AM pabloa98  wrote:

> I did not modify it.
>
> I guess I should make it bigger than 1765. is 2400 or 3200 fine?
>
> My apologies if my questions look silly. I do not know about the internal
> format of the database.
>
> Pablo
>
> On Mon, Jan 28, 2019 at 11:58 PM Andrew Gierth <
> and...@tao11.riddles.org.uk> wrote:
>
>> > "pabloa98" == pabloa98   writes:
>>
>>  pabloa98> the table baseline_denull has 1765 columns,
>>
>> Uhh...
>>
>> #define MaxHeapAttributeNumber  1600/* 8 * 200 */
>>
>> Did you modify that?
>>
>> (The back of my envelope says that on 64bit, the largest usable t_hoff
>> would be 248, of which 23 is fixed overhead leaving 225 as the max null
>> bitmap size, giving a hard limit of 1800 for MaxTupleAttributeNumber and
>> 1799 for MaxHeapAttributeNumber. And the concerns expressed in the
>> comments above those #defines would obviously apply.)
>>
>> --
>> Andrew (irc:RhodiumToad)
>>
>

Re: postgresql v11.1 Segmentation fault: signal 11: by running SELECT... JIT Issue?

[pabloa98]

I did not modify it.

I guess I should make it bigger than 1765. is 2400 or 3200 fine?

My apologies if my questions look silly. I do not know about the internal
format of the database.

Pablo

On Mon, Jan 28, 2019 at 11:58 PM Andrew Gierth 
wrote:

> > "pabloa98" == pabloa98   writes:
>
>  pabloa98> the table baseline_denull has 1765 columns,
>
> Uhh...
>
> #define MaxHeapAttributeNumber  1600/* 8 * 200 */
>
> Did you modify that?
>
> (The back of my envelope says that on 64bit, the largest usable t_hoff
> would be 248, of which 23 is fixed overhead leaving 225 as the max null
> bitmap size, giving a hard limit of 1800 for MaxTupleAttributeNumber and
> 1799 for MaxHeapAttributeNumber. And the concerns expressed in the
> comments above those #defines would obviously apply.)
>
> --
> Andrew (irc:RhodiumToad)
>