Extend inner join to fetch not yet connected rows also

[Arup Rakshit]

I have craftsmanships table which has (id, name) and users table (id, email, 
..). When a user has some craftsmanships, they are stored inside the 
contractor_skills(user_id, craftsmanship_id, id) table.

What I want here is that to list all the available craftsmanships with id, name 
and has column. I can get now only those craftsmanships that a specific user 
has, 

SELECT
craftsmanships.id,
craftsmanships.name,
TRUE as has
FROM
"craftsmanships"
INNER JOIN "contractor_skills" ON 
"contractor_skills"."craftsmanship_id" = "craftsmanships"."id"
INNER JOIN "users" ON "users"."id" = "contractor_skills"."user_id"
WHERE (contractor_skills.user_id = 8)
ORDER BY
"craftsmanships".”id"

—— 
 id |  name   | has
+-+-
  1 | paint   | t
  2 | drywall | t
(2 rows)

But I want to list all craftsmanships and has column should have `t` when 
user_id #8 has it, else `f`. How can I extend thisquery?



Thanks,

Arup Rakshit
a...@zeit.io

Re: Extend inner join to fetch not yet connected rows also

[Jan Kohnert]

Hey,

Am Sonntag, 22. September 2019, 13:21:46 CEST schrieb Arup Rakshit:
> I have craftsmanships table which has (id, name) and users table (id, email,
> ..). When a user has some craftsmanships, they are stored inside the
> contractor_skills(user_id, craftsmanship_id, id) table.

[...]

> But I want to list all craftsmanships and has column should have `t` when
> user_id #8 has it, else `f`. How can I extend thisquery?

maybe something like

select
c.id,
c.name,
case when cs.user_id = 8 then true else false end as has
from craftsmanships c
left join contractor_skills cs
on cs.craftsmanship_id = c.craftmanship_id;

-- 
MfG Jan

Re: Extend inner join to fetch not yet connected rows also

[Arup Rakshit]

Hi Jan,

I was close and came up with:

SELECT
craftsmanships.id,
craftsmanships.name,
CASE WHEN contractor_skills.user_id IS NULL THEN
FALSE
ELSE
TRUE
END AS has
FROM
"craftsmanships"
LEFT JOIN "contractor_skills" ON "contractor_skills"."craftsmanship_id" 
= "craftsmanships"."id"
LEFT JOIN "users" ON "users"."id" = "contractor_skills"."user_id"
WHERE (contractor_skills.user_id = 3
OR contractor_skills.user_id IS NULL)
ORDER BY
"craftsmanships"."id”;

But after I read yours I found mine is doing lot of unnecessary joins. Thank 
you.


Thanks,

Arup Rakshit
a...@zeit.io



> On 22-Sep-2019, at 5:38 PM, Jan Kohnert  
> wrote:
> 
> Hey,
> 
> Am Sonntag, 22. September 2019, 13:21:46 CEST schrieb Arup Rakshit:
>> I have craftsmanships table which has (id, name) and users table (id, email,
>> ..). When a user has some craftsmanships, they are stored inside the
>> contractor_skills(user_id, craftsmanship_id, id) table.
> 
> [...]
> 
>> But I want to list all craftsmanships and has column should have `t` when
>> user_id #8 has it, else `f`. How can I extend this   query?
> 
> maybe something like
> 
> select
>c.id,
>c.name,
>case when cs.user_id = 8 then true else false end as has
> from craftsmanships c
> left join contractor_skills cs
>on cs.craftsmanship_id = c.craftmanship_id;
> 
> -- 
> MfG Jan
> 
> 
> 
> 

Re: Extend inner join to fetch not yet connected rows also

[Arup Rakshit]

Hi Jan,

> On 22-Sep-2019, at 5:38 PM, Jan Kohnert  
> wrote:
> 
> Hey,
> 
> Am Sonntag, 22. September 2019, 13:21:46 CEST schrieb Arup Rakshit:
>> I have craftsmanships table which has (id, name) and users table (id, email,
>> ..). When a user has some craftsmanships, they are stored inside the
>> contractor_skills(user_id, craftsmanship_id, id) table.
> 
> [...]
> 
>> But I want to list all craftsmanships and has column should have `t` when
>> user_id #8 has it, else `f`. How can I extend this   query?
> 
> maybe something like
> 
> select
>c.id,
>c.name,
>case when cs.user_id = 8 then true else false end as has
> from craftsmanships c
> left join contractor_skills cs
>on cs.craftsmanship_id = c.craftmanship_id;
> 
> -- 
> MfG Jan
> 


But this query fetched duplicate data:

 id | name  | has
+---+-
  2 | drywall   | t
  1 | paint | t
  1 | paint | f
 11 | landscaping   | f
 12 | electrical| f
 10 | countertops   | f
 13 | plumbing  | f
  5 | flooring  | f
  8 | decks (displayed as decks and patios) | f
  6 | basements | f
  4 | kitchens  | f
  3 | bathrooms | f
 14 | handyman  | f
  9 | windows (windows and doors)   | f
  7 | carpentry | f
(15 rows)



> 

Re: Extend inner join to fetch not yet connected rows also

[Jan Kohnert]

Hi Arup,

Am Sonntag, 22. September 2019, 14:48:20 CEST schrieb Arup Rakshit:
> Hi Jan,
> 
> > On 22-Sep-2019, at 5:38 PM, Jan Kohnert 
> > wrote:
> > maybe something like
> > 
> > select
> > 
> >c.id,
> >c.name,
> >case when cs.user_id = 8 then true else false end as has
> > 
> > from craftsmanships c
> > left join contractor_skills cs
> > 
> >on cs.craftsmanship_id = c.craftmanship_id;
> 
> But this query fetched duplicate data:

yeah, that's possible, since I don't exactly know your data model. If only the 
values above are required, you could simply use distinct:

select distinct 
c.id,
c.name,
case when cs.user_id = 8 then true else false end as has 
from craftsmanships c
left join contractor_skills cs
on cs.craftsmanship_id = c.id
order by
   c.id;

-- 
MfG Jan

Re: Extend inner join to fetch not yet connected rows also

[Arup Rakshit]

> On 22-Sep-2019, at 6:55 PM, Jan Kohnert  
> wrote:
> 
> Hi Arup,
> 
> Am Sonntag, 22. September 2019, 14:48:20 CEST schrieb Arup Rakshit:
>> Hi Jan,
>> 
>>> On 22-Sep-2019, at 5:38 PM, Jan Kohnert 
>>> wrote:
>>> maybe something like
>>> 
>>> select
>>> 
>>>   c.id,
>>>   c.name,
>>>   case when cs.user_id = 8 then true else false end as has
>>> 
>>> from craftsmanships c
>>> left join contractor_skills cs
>>> 
>>>   on cs.craftsmanship_id = c.craftmanship_id;
>> 
>> But this query fetched duplicate data:
> 
> yeah, that's possible, since I don't exactly know your data model. If only 
> the 
> values above are required, you could simply use distinct:

When someone adds a craftsmanship to their skill set, the contractor_skills 
table holds that relationship. I don’t think distinct is the correct tool, as 
it will eliminate the correct data. users and craftsmanship has m:n 
relationship via the join table contractor_skills.

SELECT
craftsmanships.id,
craftsmanships.name,
CASE WHEN contractor_skills.user_id IS NULL THEN
FALSE
ELSE
TRUE
END AS has
FROM
"craftsmanships"
LEFT JOIN "contractor_skills" ON "contractor_skills"."craftsmanship_id" 
= "craftsmanships"."id"
LEFT JOIN "users" ON "users"."id" = "contractor_skills"."user_id"
WHERE (contractor_skills.user_id = 8
OR contractor_skills.user_id IS NULL)
ORDER BY
"craftsmanships"."id”;

Gives correct result. Not sure if still this query has bug in it.

id | name  | has
+---+-
  1 | paint | t
  2 | drywall   | t
  3 | bathrooms | f
  4 | kitchens  | f
  5 | flooring  | f
  6 | basements | f
  7 | carpentry | f
  8 | decks (displayed as decks and patios) | f
  9 | windows (windows and doors)   | f
 10 | countertops   | f
 11 | landscaping   | f
 12 | electrical| f
 13 | plumbing  | f
 14 | handyman  | f
(14 rows)




> 
> select distinct 
>c.id,
>c.name,
>case when cs.user_id = 8 then true else false end as has 
> from craftsmanships c
> left join contractor_skills cs
>on cs.craftsmanship_id = c.id
> order by
>   c.id;
> 
> -- 
> MfG Jan
> 
> 
> 
> 

How to represent a bi-directional list in db?

[Pankaj Jangid]

I am creating an application for a manufacturing scenario. To represent
stages in an assembly line, I wanted to create following table,

CREATE TABLE stages (
   id SERIAL PRIMARY KEY,
   name  VARCHAR(80) NOT NULL,
   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   prev_stage_id SERIAL REFERENCES stages NULL,
   next_stage_id SERIAL REFERENCES stages NULL,
   process_id SERIAL REFERENCES processes NOT NULL
);

But it:

Failed with: conflicting NULL/NOT NULL declarations for column
"prev_stage_id" of table "stages"

Is it not possible to create "nullable" self referencing foreign keys?

-- 
Pankaj Jangid

Re: How to represent a bi-directional list in db?

[Francisco Olarte]

Pankaj:

On Sun, Sep 22, 2019 at 4:25 PM Pankaj Jangid  wrote:
> CREATE TABLE stages (
>id SERIAL PRIMARY KEY,
>name  VARCHAR(80) NOT NULL,
>created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
>updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
>prev_stage_id SERIAL REFERENCES stages NULL,
>next_stage_id SERIAL REFERENCES stages NULL,
>process_id SERIAL REFERENCES processes NOT NULL
> );
> Failed with: conflicting NULL/NOT NULL declarations for column
> "prev_stage_id" of table "stages"
> Is it not possible to create "nullable" self referencing foreign keys?

Serial seems wrong. It means integer, not null, defaul next value from
a sequence.

What you probably want is just "prev_stage_id INTEGER" ( NULL by
default ), as you do not want the prev/next stage ids to be generated,
you normally would want to assign values from other tuples.

Also, you may have problems populating this kind of table, as you will
not have the ids from either prev or next stage when building it.

And lastly, in SQL you do not really need a doubly linked list, just
populate prev_stage_id, and index it and you can query next stage of a
tuple using it.

Francisco Olarte.

Re: Extend inner join to fetch not yet connected rows also

[Adrian Klaver]

On 9/22/19 6:30 AM, Arup Rakshit wrote:



On 22-Sep-2019, at 6:55 PM, Jan Kohnert  wrote:

Hi Arup,

Am Sonntag, 22. September 2019, 14:48:20 CEST schrieb Arup Rakshit:

Hi Jan,


On 22-Sep-2019, at 5:38 PM, Jan Kohnert 
wrote:
maybe something like

select

   c.id,
   c.name,
   case when cs.user_id = 8 then true else false end as has

from craftsmanships c
left join contractor_skills cs

   on cs.craftsmanship_id = c.craftmanship_id;


But this query fetched duplicate data:


yeah, that's possible, since I don't exactly know your data model. If only the
values above are required, you could simply use distinct:


When someone adds a craftsmanship to their skill set, the contractor_skills 
table holds that relationship. I don’t think distinct is the correct tool, as 
it will eliminate the correct data. users and craftsmanship has m:n 
relationship via the join table contractor_skills.

SELECT
craftsmanships.id,
craftsmanships.name,
CASE WHEN contractor_skills.user_id IS NULL THEN
FALSE
ELSE
TRUE
END AS has
FROM
"craftsmanships"
LEFT JOIN "contractor_skills" ON "contractor_skills"."craftsmanship_id" = 
"craftsmanships"."id"
LEFT JOIN "users" ON "users"."id" = "contractor_skills"."user_id"
WHERE (contractor_skills.user_id = 8
OR contractor_skills.user_id IS NULL)
ORDER BY
"craftsmanships"."id”;

Gives correct result. Not sure if still this query has bug in it.


What I see is that the rows below with 'has' = 'f' will not have a 
user_id(implied). So I am not sure how you plan to associate that data 
with a user?




id | name  | has
+---+-
   1 | paint | t
   2 | drywall   | t
   3 | bathrooms | f
   4 | kitchens  | f
   5 | flooring  | f
   6 | basements | f
   7 | carpentry | f
   8 | decks (displayed as decks and patios) | f
   9 | windows (windows and doors)   | f
  10 | countertops   | f
  11 | landscaping   | f
  12 | electrical| f
  13 | plumbing  | f
  14 | handyman  | f
(14 rows)






select distinct
c.id,
c.name,
case when cs.user_id = 8 then true else false end as has
from craftsmanships c
left join contractor_skills cs
on cs.craftsmanship_id = c.id
order by
   c.id;

--
MfG Jan












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

Re: Extend inner join to fetch not yet connected rows also

[John W Higgins]

On Sun, Sep 22, 2019 at 6:30 AM Arup Rakshit  wrote:

>
>
> SELECT
> craftsmanships.id,
> craftsmanships.name,
> CASE WHEN contractor_skills.user_id IS NULL THEN
> FALSE
> ELSE
> TRUE
> END AS has
> FROM
> "craftsmanships"
> LEFT JOIN "contractor_skills" ON
> "contractor_skills"."craftsmanship_id" = "craftsmanships"."id"
> LEFT JOIN "users" ON "users"."id" = "contractor_skills"."user_id"
> WHERE (contractor_skills.user_id = 8
> OR contractor_skills.user_id IS NULL)
> ORDER BY
> "craftsmanships"."id”;
>
> Gives correct result. Not sure if still this query has bug in it.
>
>
If you do not understand the query - then it's wrong on its face. You
should never run something which you do not understand.

So one should take a step back - make smaller pieces and then combine
smaller pieces of logic together to form an answer. If at some point in the
future there is a performance issue - then deal with that then - but do not
make some fancy multi join query that you do not fully understand.

So in that vein,

Piece 1 = A list of craftsmanship_id for a particular user
Piece 2 - Take piece 1 and compare to the full list of craftsmanship_id

Putting piece 1 into a CTE you end up with something like this.

with UserSkills as (
SELECT
  craftsmanship_id
FROM
  contractor_skills
WHERE
  user_id = 3
)
SELECT
  craftsmanships.id,
  craftsmanships.name,
  CASE WHEN UserSkills.ctraftsmanship_id IS NULL THEN FALSE
  ELSE TRUE as has
FROM
  craftsmanships
LEFT JOIN
  UserSkills
ON
  craftsmanships.id = UserSkills.craftsmanship_id

So you take the two pieces and combine then. Yes you can drop the CTE into
the main body - but unless you are certain you are doing it correctly -
there is no point doing that. The query parser will do the work for you -
so why bother making your life more difficult then it need be.

John W Higgins

Re: Extend inner join to fetch not yet connected rows also

[Jan Kohnert]

Hi Arup,

Am Sonntag, 22. September 2019, 15:30:38 CEST schrieb Arup Rakshit:
> When someone adds a craftsmanship to their skill set, the contractor_skills
> table holds that relationship. I don’t think distinct is the correct tool,
> as it will eliminate the correct data. users and craftsmanship has m:n
> relationship via the join table contractor_skills.

depending on the definition of table "contractor_skills" it can give you a n:m 
relationship between user_id and craftmanship_id, that is true.
 
> SELECT
>   craftsmanships.id,
>   craftsmanships.name,
>   CASE WHEN contractor_skills.user_id IS NULL THEN
>   FALSE
>   ELSE
>   TRUE
>   END AS has
> FROM
>   "craftsmanships"
>   LEFT JOIN "contractor_skills" ON 
"contractor_skills"."craftsmanship_id" =
> "craftsmanships"."id" LEFT JOIN "users" ON "users"."id" =
> "contractor_skills"."user_id" WHERE (contractor_skills.user_id = 8
>   OR contractor_skills.user_id IS NULL)
> ORDER BY
>   "craftsmanships"."id”;

BUT: you don't use any of users' columns in select, where, or order by. And 
since users is in a left join it is just a table which is neither used nor 
relevant in that particular statement.

In the end, it depends on how data is structured in your database and what you 
want to achieve.

-- 
MfG Jan

Re: Use of ?get diagnostics'?

[Thiemo Kellner]

Hi Andrew


Paste sites are for IRC, on the mailing list you should always attach
the necessary details to your message.


Ok, I was under the impression that paste site were preferable to  
attachments which generates traffic not everyone is interested in.



 Thiemo>   the following exception was thrown:
 Thiemo> SQLSTATE: 42703
 Thiemo> column "row_count" does not exist

line 44 of your paste:  V_TEXT := V_TEXT || ROW_COUNT || ' row.';

should be V_ROW_COUNT, I suspect. Likewise line 46.


You are perfectly right and now I feel a bit stupid. Many thanks!

Maybe others had the same idea, but it would help me, if the exception  
contained a line where the error was found. Though, I am not quite  
sure whether this is just due to my error handling in the function.


Kind regards

Thiemo

--
S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:  
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B

Re: When does Postgres use binary I/O?

[Tom Lane]

I wrote:
> The major thing that this style of test misses, IMO, is that it
> proves nothing at all about the stability of the binary representation.
> In particular, it'd be almost trivially easy for someone to screw up
> the expectation of endianness independence for the binary format.

> To address that, I think we'd not only want to check the round-trip
> behavior, but also compare the binary output to a "known good"
> reference file.  If we messed up on endianness independence, the
> bigendian members of the buildfarm would find it immediately.

I thought of an easier-to-maintain approach to that part than having
a reference file.  Binary send functions are invokable from SQL,
so we could just imagine adding test cases along the lines of

regression=# select int8send(42);
  int8send  

 \x002a
(1 row)

for each data type.  This would be enough to detect endianness issues,
garbage in padding bytes, etc.

The receive functions are not so easy to call, so we still need a
round-trip test, but you could imagine a TAP test framework for that.
Or, perhaps, the thing to do would be to provide a generic test function
that takes a value, runs it through the type's send and then receive
functions, and returns the result (or just complains if it gets different
bits out ...)

regards, tom lane

Help: Postgres Replication issues with pacemaker

[Shital A]

Hello,

We have setup active-passive cluster using streaming replication on Rhe
7.5. We are testing pacemaker for automated failover.
We are seeing below issues with the setup :

1. When a failoveris triggered when data is being added to the primary by
killing primary (killall -9 postgres), the standby doesnt come up in sync.
On pacemaker, the crm_mon -Afr shows standby in disconnected and HS:alone
state.

On postgres, we see below error:

< 2019-09-20 17:07:46.266 IST > LOG:  entering standby mode
< 2019-09-20 17:07:46.267 IST > LOG:  database system was not properly shut
down; automatic recovery in progress
< 2019-09-20 17:07:46.270 IST > LOG:  redo starts at 1/680A2188
< 2019-09-20 17:07:46.370 IST > LOG:  consistent recovery state reached at
1/6879D9F8
< 2019-09-20 17:07:46.370 IST > LOG:  database system is ready to accept
read only connections
cp: cannot stat
'/var/lib/pgsql/9.6/data/archivedir/000100010068': No such file
or directory
< 2019-09-20 17:07:46.751 IST > LOG:  statement: select pg_is_in_recovery()
< 2019-09-20 17:07:46.782 IST > LOG:  statement: show
synchronous_standby_names
< 2019-09-20 17:07:50.993 IST > LOG:  statement: select pg_is_in_recovery()
< 2019-09-20 17:07:53.395 IST > LOG:  started streaming WAL from primary at
1/6800 on timeline 1
< 2019-09-20 17:07:53.436 IST > LOG:  invalid contrecord length 2662 at
1/6879D9F8
< 2019-09-20 17:07:53.438 IST > FATAL:  terminating walreceiver process due
to administrator command
cp: cannot stat '/var/lib/pgsql/9.6/data/archivedir/0002.history': No
such file or directory
cp: cannot stat
'/var/lib/pgsql/9.6/data/archivedir/000100010068': No such file
or directory

When we try to restart postgres on the standby, using pg_ctl restart, the
standby start syncing.


2. After standby syncs using pg_ctl restart as mentioned above, we found
out that 1-2 records are missing on the standby.

Need help to check:
1. why the standby fails to start in the first place and complains about
missing logs?
2. can record mismatch be a problem related to failover not successful?

If you have faced this issue/have knowledge, please let us know.

replication is async.
recovery.conf file has restore_command that uses cp


Thanks.

Re: Use of ?get diagnostics'?

[Adrian Klaver]

On 9/22/19 11:33 AM, Thiemo Kellner wrote:

Hi Andrew


Paste sites are for IRC, on the mailing list you should always attach
the necessary details to your message.


Ok, I was under the impression that paste site were preferable to 
attachments which generates traffic not everyone is interested in.



 Thiemo>   the following exception was thrown:
 Thiemo> SQLSTATE: 42703
 Thiemo> column "row_count" does not exist

line 44 of your paste:  V_TEXT := V_TEXT || ROW_COUNT || ' row.';

should be V_ROW_COUNT, I suspect. Likewise line 46.


You are perfectly right and now I feel a bit stupid. Many thanks!

Maybe others had the same idea, but it would help me, if the exception 
contained a line where the error was found. Though, I am not quite sure 
whether this is just due to my error handling in the function.


It should:

create table diag_test(id integer);

insert into diag_test values (1), (2);

CREATE OR REPLACE FUNCTION public.get_diag_test()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
V_ROW_COUNT  BIGINT DEFAULT 0;
V_TEXT text;
BEGIN

PERFORM * FROM diag_test;
get current diagnostics V_ROW_COUNT = ROW_COUNT;
V_TEXT := ROW_COUNT || ' row.';
END;
$function$

test=# select get_diag_test();
ERROR:  column "row_count" does not exist
LINE 1: SELECT ROW_COUNT || ' row.'
   ^
QUERY:  SELECT ROW_COUNT || ' row.'
CONTEXT:  PL/pgSQL function get_diag_test() line 9 at assignment

To get above I believe you will need to use GET CURRENT DIAGNOSTICS 
PG_CONTEXT:


https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

and example:

https://www.postgresql.org/docs/11/plpgsql-control-structures.html#PLPGSQL-CALL-STACK




Kind regards

Thiemo




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

Re: Extend inner join to fetch not yet connected rows also

[Michael Lewis]

Just a side note, that the below can be written more simply-


CASE WHEN UserSkills.craftsmanship_id IS NULL THEN FALSE
  ELSE TRUE as has


If you want to be direct-

UserSkills.craftsmanship_id IS NOT NULL as has

Re: When does Postgres use binary I/O?

[Paul A Jungwirth]

On Sun, Sep 22, 2019 at 11:53 AM Tom Lane  wrote:
> I thought of an easier-to-maintain approach to that part than having
> a reference file.  Binary send functions are invokable from SQL,
> so we could just imagine adding test cases along the lines of
>
> regression=# select int8send(42);
>   int8send
> 
>  \x002a
> (1 row)
>
> for each data type.  This would be enough to detect endianness issues,
> garbage in padding bytes, etc.

I just finished my multirange patch (well, "finished" :-), so I might
be willing to sign up for this. Would you scatter these tests around
in the various existing files? Or add a new cross-cutting file (like
opr_sanity etc)?

> The receive functions are not so easy to call, so we still need a
> round-trip test, but you could imagine a TAP test framework for that.
> Or, perhaps, the thing to do would be to provide a generic test function
> that takes a value, runs it through the type's send and then receive
> functions, and returns the result (or just complains if it gets different
> bits out ...)

So you're saying the latter option is to add a new function that
someone can call from SQL, that just round-trips a value through send
+ recv? And then call that from an ordinary regress test? I guess the
tests themselves can't define the function (like they define
binary_coercible), because you need to call *_recv from C, so this
would actually be a function we ship and document, right? That seems
within my abilities.

Should I move this thread over to pgsql-hackers for this?

Paul

Re: When does Postgres use binary I/O?

[Tom Lane]

Paul A Jungwirth  writes:
> On Sun, Sep 22, 2019 at 11:53 AM Tom Lane  wrote:
>> I thought of an easier-to-maintain approach to that part than having
>> a reference file.

> I just finished my multirange patch (well, "finished" :-), so I might
> be willing to sign up for this. Would you scatter these tests around
> in the various existing files? Or add a new cross-cutting file (like
> opr_sanity etc)?

I think adding them to the existing datatype-specific regression tests
is probably the way to go.  It seems like it'd be more likely that
someone writing a new datatype would emulate one of those test scripts
than that they'd notice they ought to add a section to some other
script.

> So you're saying the latter option is to add a new function that
> someone can call from SQL, that just round-trips a value through send
> + recv? And then call that from an ordinary regress test?

Yeah, something roughly like "send_recv_round_trip(any) returns bool",
I guess.

> I guess the
> tests themselves can't define the function (like they define
> binary_coercible), because you need to call *_recv from C, so this
> would actually be a function we ship and document, right? That seems
> within my abilities.

I'm not sure we'd want to expose it as a generally available function.
One idea is to put it in regress.c, although most of the functions
in there today are not created till create_function_1.sql which runs
too late to be useful for this.  Maybe it's okay as a core function.

> Should I move this thread over to pgsql-hackers for this?

Yeah.

regards, tom lane

Re: How to represent a bi-directional list in db?

[Pankaj Jangid]

Francisco Olarte  writes:

> On Sun, Sep 22, 2019 at 4:25 PM Pankaj Jangid  wrote:
>> CREATE TABLE stages (
>>id SERIAL PRIMARY KEY,
>>name  VARCHAR(80) NOT NULL,
>>created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
>>updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
>>prev_stage_id SERIAL REFERENCES stages NULL,
>>next_stage_id SERIAL REFERENCES stages NULL,
>>process_id SERIAL REFERENCES processes NOT NULL
>> );
>> Failed with: conflicting NULL/NOT NULL declarations for column
>> "prev_stage_id" of table "stages"
>> Is it not possible to create "nullable" self referencing foreign keys?
>
> Serial seems wrong. It means integer, not null, defaul next value from
> a sequence.
>
> What you probably want is just "prev_stage_id INTEGER" ( NULL by
> default ), as you do not want the prev/next stage ids to be generated,
> you normally would want to assign values from other tuples.
>

Thanks. This resolved my problem of NULL/NOT NULL conflict. I wasn't
aware that SERIAL is by default NOT NULL.

> Also, you may have problems populating this kind of table, as you will
> not have the ids from either prev or next stage when building it.
>

If NULL value is allowed I can fill it up with NULL initially. Right? Or
is there something wrong here.

> And lastly, in SQL you do not really need a doubly linked list, just
> populate prev_stage_id, and index it and you can query next stage of a
> tuple using it.
>

Could you please elaborate? Suppose I have this table,

CREATE TABLE stages (
id SERIAL PRIMARY KEY,
name VARCHAR(80) NOT NULL,
next_id INTEGER REFERENCE stages NULL,
);

What would be the backward query in that case? Forward is clear. This is
forward query,

SELECT name FROM stages WHERE next_id = 123;

-- 
Pankaj Jangid

Re: Help: Postgres Replication issues with pacemaker

[Shital A]

On Mon, 23 Sep 2019, 00:46 Shital A,  wrote:

>
> Hello,
>
> We have setup active-passive cluster using streaming replication on Rhe
> 7.5. We are testing pacemaker for automated failover.
> We are seeing below issues with the setup :
>
> 1. When a failoveris triggered when data is being added to the primary by
> killing primary (killall -9 postgres), the standby doesnt come up in sync.
> On pacemaker, the crm_mon -Afr shows standby in disconnected and HS:alone
> state.
>
> On postgres, we see below error:
>
> < 2019-09-20 17:07:46.266 IST > LOG:  entering standby mode
> < 2019-09-20 17:07:46.267 IST > LOG:  database system was not properly
> shut down; automatic recovery in progress
> < 2019-09-20 17:07:46.270 IST > LOG:  redo starts at 1/680A2188
> < 2019-09-20 17:07:46.370 IST > LOG:  consistent recovery state reached at
> 1/6879D9F8
> < 2019-09-20 17:07:46.370 IST > LOG:  database system is ready to accept
> read only connections
> cp: cannot stat
> '/var/lib/pgsql/9.6/data/archivedir/000100010068': No such file
> or directory
> < 2019-09-20 17:07:46.751 IST > LOG:  statement: select pg_is_in_recovery()
> < 2019-09-20 17:07:46.782 IST > LOG:  statement: show
> synchronous_standby_names
> < 2019-09-20 17:07:50.993 IST > LOG:  statement: select pg_is_in_recovery()
> < 2019-09-20 17:07:53.395 IST > LOG:  started streaming WAL from primary
> at 1/6800 on timeline 1
> < 2019-09-20 17:07:53.436 IST > LOG:  invalid contrecord length 2662 at
> 1/6879D9F8
> < 2019-09-20 17:07:53.438 IST > FATAL:  terminating walreceiver process
> due to administrator command
> cp: cannot stat '/var/lib/pgsql/9.6/data/archivedir/0002.history': No
> such file or directory
> cp: cannot stat
> '/var/lib/pgsql/9.6/data/archivedir/000100010068': No such file
> or directory
>
> When we try to restart postgres on the standby, using pg_ctl restart, the
> standby start syncing.
>
>
> 2. After standby syncs using pg_ctl restart as mentioned above, we found
> out that 1-2 records are missing on the standby.
>
> Need help to check:
> 1. why the standby fails to start in the first place and complains about
> missing logs?
> 2. can record mismatch be a problem related to failover not successful?
>
> If you have faced this issue/have knowledge, please let us know.
>
> replication is async.
> recovery.conf file has restore_command that uses cp
>
>
> Thanks.
>

Hello Team,


Any ideas?

Thanks..

>
>

Re: pgbouncer with ldap

[Achilleas Mantzios]

On 23/9/19 9:10 π.μ., Ayub M wrote:
Achilleas, I am struggling with libpam_ldap package install. Does its setup also involves sssd daemon with nss and pam? I am seeing many different ways of dealing with ldap with pam and different 
modules which I am not familiar with (nscld, sssd etc). If you have any steps documented for installing libpam_ldap kindly share with me.




Hello,
you have to just (for debian based) :
apt install libpam-ldap
did you do that? Did you correctly answered the conf questions? No additional packages needed IIRC. PAM has many modules, module for LDAP is one of them. Module for SSH is another. Read up a little 
bit on the PAM architecture (an old but very successful SUN Microsystems technology) and things will be clearer.

Things to note :
There are two ldap conf files in your /etc :
1) /etc/ldap/ldap.conf : the classic ldap client conf file, used by e.g. 
ldapsearch
2) /etc/ldap.conf : the conf file of libpam-ldap




On Wed, Sep 18, 2019 at 9:48 AM Ayub M mailto:hia...@gmail.com>> wrote:

Will do, thanks!

On Wed, Sep 18, 2019, 4:55 AM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote:

Thanx I am really glad I could help! pgbouncer and LDAP is a PITA!

It could be nice if you replied to the list thread just to have an 
update on how it goes.


On 18/9/19 11:23 π.μ., Ayub M wrote:

Hi Achilleas, yes got the pam authentication working. I had to create 
pgbouncer file in pam directory. Now I am working on second part, which is 
integration of pam with ldap. Getting ldap
creds, will soon update you on how it goes thanks for your help. 
Without your article I would have given up on this option..

On Wed, Sep 18, 2019, 1:53 AM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote:

Hello

any luck?

On 14/9/19 9:41 π.μ., Ayub M wrote:

Yes I did set that, here is how pgbouncer looks like ---

-rwsrwsr-x. 1 root root 2087504 Sep 13 00:45 pgbouncer


On Fri, Sep 13, 2019 at 6:50 AM Achilleas Mantzios 
mailto:ach...@matrix.gatewaynet.com>> wrote:

On 13/9/19 10:19 π.μ., Ayub M wrote:

Stumbled in the first step - PAM authentication via pgbouncer. 
After compiling pgbouncer with the pam plug-in, I am unable to login into the 
db - throws PAM error message. Please
help.

User created with the same password as linux user --
localhost:~$ psql -h dbhost -p 3306 -U admin -W db1
db1=> create user testuser password 'hello123';
CREATE ROLE

[ec2-user@ip-1.1.1.1  pam.d]$ psql 
-h localhost -p 5432 testdb -U testuser
Password for user testuser:
psql: ERROR:  auth failed


ok, pgbouncer should be able to read /etc/pam* files.
Did you miss the
|# chown root:staff ~pgbouncer/pgbouncer-1.9.0/pgbouncer |
|# chmod +s ~pgbouncer/pgbouncer-1.9.0/pgbouncer|
part?



Log entries - pgbouncer.log
2019-09-13 06:51:47.180 UTC [5752] LOG C-0x1243020: 
testdb/testuser@[::1]:52408 login attempt: db=testdb user=testuser tls=no
2019-09-13 06:51:47.180 UTC [5752] NOISE safe_send(12, 9) = 9
2019-09-13 06:51:47.180 UTC [5752] NOISE resync(12): done=86, 
parse=86, recv=86
2019-09-13 06:51:47.180 UTC [5752] NOISE resync(12): done=0, 
parse=0, recv=0
2019-09-13 06:51:47.180 UTC [5752] NOISE safe_recv(12, 4096) = 
14
2019-09-13 06:51:47.180 UTC [5752] NOISE C-0x1243020: 
testdb/testuser@[::1]:52408 read pkt='p' len=14
2019-09-13 06:51:47.180 UTC [5752] DEBUG C-0x1243020: 
testdb/testuser@[::1]:52408 pam_auth_begin(): pam_first_taken_slot=1, 
pam_first_free_slot=1
2019-09-13 06:51:47.180 UTC [5752] DEBUG pam_auth_worker(): 
processing slot 1
2019-09-13 06:51:47.180 UTC [5752] WARNING pam_authenticate() 
failed: Authentication failure
2019-09-13 06:51:47.181 UTC [5752] DEBUG pam_auth_worker(): 
authorization completed, status=3
2019-09-13 06:51:47.386 UTC [5752] LOG C-0x1243020: 
testdb/testuser@[::1]:52408 closing because: auth failed (age=0s)
2019-09-13 06:51:47.386 UTC [5752] WARNING C-0x1243020: 
testdb/testuser@[::1]:52408 pooler error: auth failed

Able to login as testuser
[ec2-user@ip-1.1.1.1  pam.d]$ su - 
testuser
Password:
Last login: Fri Sep 13 06:21:12 UTC 2019 on pts/1
[testuser@ip-1.1.1.1  ~]$ id
uid=1001(testuser) gid=1001(testuser) groups=1001(testuser) 
context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

The user w