Re: [GENERAL] browser interface to forums please?

[Pavel Stehule]

Hi

2017-03-25 5:49 GMT+01:00 Yuri Budilov :

> Hello everyone
> Can these forums be moved to internet ?
>

It is on internet


> All these emails is so 1990s.
>

And it is working well - there is not spam and ballast


> So hard to follow, so hard to search for historical answers.
> We really need to be able to post via browser.
>

why?

there is a fulltexted archive

https://www.postgresql.org/list/group/2/
https://www.postgresql.org/list/pgsql-general/

You can use http://stackoverflow.com/ too

Regards

Pavel




> best regards to everyone
>
>

[GENERAL] browser interface to forums please?

[Yuri Budilov]

Hello everyone
Can these forums be moved to internet ?
All these emails is so 1990s.
So hard to follow, so hard to search for historical answers.
We really need to be able to post via browser.

best regards to everyone

Re: [GENERAL] The same query is too slow in some time of execution

[DrakoRod]

You're right, I'm sorry.

At the moment, we review the schema for tables and indexes and decided
redesigned. I detected a loop in the join, because we only have a integer
sequencial like PK  and no composite keys in the tables. I think that is the
mainly problem.

Thanks! 



-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://www.postgresql-archive.org/The-same-query-is-too-slow-in-some-time-of-execution-tp5951060p5951841.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Combining INSERT with DELETE RETURNING

[Thomas Kellerer]

Alexander Farber schrieb am 24.03.2017 um 16:06:

> the doc https://www.postgresql.org/docs/9.6/static/sql-delete.html states:
> 
> "The syntax of the RETURNING list is identical to that of the output list of 
> SELECT."
> 
> So I am trying to rewrite the INSERT .. ON CONFLICT DO NOTHING followed by a 
> DELETE:
> into a single statement:
> 
> INSERT INTO words_reviews (
> uid,
> author,
> nice,
> review,
> updated
> ) VALUES (
> DELETE FROM words_reviews
> WHERE author <> out_uid
> AND author = ANY(_uids)
> RETURNING
> uid,
> out_uid,-- change to out_uid
> nice,
> review,
> updated
> )
> ON CONFLICT DO NOTHING;

You need a CTE:

with deleted as (
DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids)
RETURNING
uid,
out_uid,-- change to out_uid
nice,
review,
updated
)
INSERT INTO words_reviews (uid, author, nice, review, updated)
select *
from deleted
ON CONFLICT DO NOTHING;



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Combining INSERT with DELETE RETURNING

[David G. Johnston]

On Fri, Mar 24, 2017 at 8:06 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> Good afternoon,
>
> the doc https://www.postgresql.org/docs/9.6/static/sql-delete.html
> states:
>
> "The syntax of the RETURNING list is identical to that of the output list
> of SELECT."
> ​[...]​
>
> words=> \i words_merge_users.sql
> psql:words_merge_users.sql:218: ERROR:  syntax error at or near "FROM"
> LINE 131: DELETE FROM words_reviews
>

​That doesn't work for SELECT either...

INSERT INTO tbl_trg (id)
VALUES (
SELECT 1
);

ERROR: syntax error at or near "SELECT"

This should help with the SELECT variation:

https://www.postgresql.org/docs/9.5/static/sql-insert.html

That said the page does say:

"query
A query (SELECT statement) that supplies the rows to be inserted. Refer to
the SELECT statement for a description of the syntax.
"

So directly replace the actual SELECT query with a DELETE-RETURNING doesn't
seem to work.  You will need to perform the DELETE separately and then
funnel those records through a SELECT statement.  A subquery may work
though a CTE is likely considered best practice.

David J.

[GENERAL] Combining INSERT with DELETE RETURNING

[Alexander Farber]

Good afternoon,

the doc https://www.postgresql.org/docs/9.6/static/sql-delete.html states:

"The syntax of the RETURNING list is identical to that of the output list
of SELECT."

So I am trying to rewrite the INSERT .. ON CONFLICT DO NOTHING followed by
a DELETE:

INSERT INTO words_reviews (
uid,
author,
nice,
review,
updated
) SELECT
uid,
out_uid,-- change to out_uid
nice,
review,
updated
FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids)
ON CONFLICT DO NOTHING;

DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids);

into a single statement:

INSERT INTO words_reviews (
uid,
author,
nice,
review,
updated
) VALUES (
DELETE FROM words_reviews
WHERE author <> out_uid
AND author = ANY(_uids)
RETURNING
uid,
out_uid,-- change to out_uid
nice,
review,
updated
)
ON CONFLICT DO NOTHING;

but get the syntax error:

words=> \i words_merge_users.sql
psql:words_merge_users.sql:218: ERROR:  syntax error at or near "FROM"
LINE 131: DELETE FROM words_reviews
 ^

What am I doing wrong this time please?

Thank you
Alex

P.S. Below is my custom function in its entirety + table descriptions:

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,

created timestamptz NOT NULL,
visited timestamptz NOT NULL,
ip inet NOT NULL,

fcm varchar(255),
apnsvarchar(255),
motto   varchar(255),

vip_until   timestamptz,
grand_until timestamptz,

banned_until timestamptz,
banned_reason varchar(255) CHECK (LENGTH(banned_reason) > 0),

elointeger NOT NULL CHECK (elo >= 0),
medals integer NOT NULL CHECK (medals >= 0),
coins  integer NOT NULL
);

CREATE TABLE words_social (
sid varchar(255) NOT NULL,

social integer NOT NULL CHECK (0 <= social AND social <= 6),
female integer NOT NULL CHECK (female = 0 OR female = 1),
given  varchar(255) NOT NULL CHECK (given ~ '\S'),
family varchar(255),
photo  varchar(255) CHECK (photo ~* '^https?://...'),
place  varchar(255),
stamp  integer NOT NULL,

uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

CREATE OR REPLACE FUNCTION words_merge_users(
in_users jsonb,
in_ip inet,
OUT out_uid integer,
OUT out_vip timestamptz,
OUT out_grand timestamptz,
OUT out_banned timestamptz,
OUT out_reason varchar
) RETURNS RECORD AS
$func$
DECLARE
_user  jsonb;
_uids  integer[];
-- the variables below are used to temporary save new user stats
_created   timestamptz;
_elo   integer;
_medalsinteger;
_coins integer;
BEGIN
-- in_users must be a JSON array with at least 1 element
IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
RAISE EXCEPTION 'Invalid users = %', in_users;
END IF;

_uids := (
SELECT ARRAY_AGG(DISTINCT uid)
FROM words_social
JOIN JSONB_ARRAY_ELEMENTS(in_users) x
ON sid = x->>'sid'
AND social = (x->>'social')::int
);

IF _uids IS NULL THEN
-- no users found -> create a new user
INSERT INTO words_users (
created,
visited,
ip,
elo,
medals,
coins
) VALUES (
CURRENT_TIMESTAMP,
CURRENT_TIMESTAMP,
in_ip,
1500,
  

Re: [GENERAL] postgres source code function "internal_ping" may be not right in some conditions

[Tom Lane]

lin  writes:
>  all.  I have test the function "internal_ping", and find in some conditions, 
> the return result is not right.

> if conn->status == CONNECTION_BAD  ,  and the  "conn->last_sqlstate" is 
> "28000", the return value is PQPING_OK, it is not right.

I don't think so.  28000 is ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION,
which is a not-unlikely case here since often a caller wouldn't have
bothered to provide a valid userid.  However, if the server returned that,
then it must be up; otherwise it could not have looked into pg_authid
to find out that the supplied userid wasn't valid.

IOW, the point of the ping functionality is to test whether the server
is up, not whether you have valid login credentials.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Run statements before pg_dump in same transaction?

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


François Beausoleil asked:
> To that end, we'd like to anonymize the data before it leaves the database 
> server.
>
> One solution we thought of would be to run statements prior to pg_dump, but 
> within 
> the same transaction, something like this:
>
> BEGIN;
> UPDATE users SET email = 'dev+' || id || '@example.com', password_hash = '/* 
> hash of "password" */', ...;
> -- launch pg_dump as usual, ensuring a ROLLBACK at the end
...
> Is there a ready-made solution for this?

No - at least not with generating a dump and scrubbing *before* the data comes 
out. Some other ideas:

* Periodically do a full dump to another database under your control, sanitize 
the 
data, and make all dev dumps come from *that* database. Process roughly becomes:

* pg_dump herokudb | psql dev_temp
* 
* drop existing dev_old; rename devdb to dev_old; rename dev_temp to devdb
* Devs can pg_dump devdb at will

That still moves your sensitive data to another server though, even temporarily.
Another approach is to use the -T flag of pg_dump to exclude certain tables. 
Make modified copies of them on the server, then rename them after the dump 
(or simply put them in a new namespace):

* (create a dev.users identical (including indexes, etc.) to public.users)
* truncate table dev.users;
* insert into dev.users select * from public.users;
* update dev.users set email = 'dev' + || ...etc. 
* pg_dump -d  -T public.users > devs_use_this.pg

Then dev could do: set schema = dev, public;

Or you could simply move the sanitized table back:

alter table dev.users set schema public;

If you are going to rename, it may be simpler to not make an identical copy 
of the affected tables (i.e. with indexes) but just a data-only copy:

create table dev.users as select * from public.users;

pg_dump herokudb --schema-only | psql devdb
pg_dump herokudb --data-only -T public.users | psql devdb
psql devdb -c 'insert into public.users select * from dev.users'


- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201703240911
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAljVHHsACgkQvJuQZxSWSshUbgCg7TzCkAzT4wKoKd5/2rruzLte
TJcAoI7AvGdGzlNp5b3N+LFJ9DWIZ8/C
=7heB
-END PGP SIGNATURE-




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] postgres source code function "internal_ping" may be not right in some conditions

[lin]

Hi,
 all.  I have test the function "internal_ping", and find in some conditions, 
the return result is not right.


the code, you can see :
http://code.taobao.org/p/pgsql953/src/src/interfaces/libpq/fe-connect.c


if conn->status == CONNECTION_BAD  ,  and the  "conn->last_sqlstate" is 
"28000", the return value is PQPING_OK, it is not right.


Thanks,
 wln

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

[Glen Huang]

Yes, the order doesn't matter, and this approach sounds like a good idea. I'll 
try it out, thanks.

> On 23 Mar 2017, at 3:56 PM, Alban Hertroys  wrote:
> 
>> 
>> On 22 Mar 2017, at 17:54, Glen Huang  wrote:
>> 
>> Hello,
>> 
>> If I have a table like
>> 
>> CREATE TABLE relationship (
>> obj1 INTEGER NOT NULL REFERENCES object, 
>> obj2 INTEGER NOT NULL REFERENCES object,
>> obj3 INTEGER NOT NULL REFERENCES object,
>> ...
>> )
>> 
>> And I want to constrain that if 1,2,3 is already in the table, rows like 
>> 1,3,2 or 2,1,3 shouldn't be allowed.
>> 
>> Is there a general solution to this problem?
> 
> Does the order of the values of (obj1, obj2, obj3) in relationship matter? If 
> not, you could swap them around on INSERT/UPDATE to be in sorted order. I'd 
> probably go with a BEFORE INSERT OR UPDATE trigger.
> 
> In addition, to prevent unsorted entry, on obj2 add CHECK (obj2 > obj1) and 
> on obj3 add CHECK (obj3 > obj2).
> 
> Now you can create a normal PK or unique key on (obj1, obj2, obj3) as the 
> order of their values is not variable anymore.
> 
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.

Re: [GENERAL] Lag in asynchronous replication

[Michael Paquier]

On Fri, Mar 24, 2017 at 3:11 PM, Subhankar Chattopadhyay
 wrote:
> Are you asking to have slave with synchronous replication?

(top-posting is annoying)

No, slaves cannot do synchronous replication. I am just telling that
once you are sure that a sync state has been achieved on the master,
you have the guarantee that data gets synchronously replicated on the
standbys as long as you do *not* change synchronous_standby_names. So
there is no actual need to know what's the state of the master during
a failover to a sync standby.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Lag in asynchronous replication

[Subhankar Chattopadhyay]

Hi Michael,

Are you asking to have slave with synchronous replication?

Regards,
SUBHANKAR CHATTOPADHYAY

On 24 Mar 2017 09:33, "Michael Paquier"  wrote:

> On Thu, Mar 23, 2017 at 11:37 PM, Subhankar Chattopadhyay
>  wrote:
> > in case of automated failover i want to check if slave is lagging from
> > master and only if it is in sync, i want to do failover. But I am
> working in
> > a virtual cloud environment so by that time the master VM may not be
> > available to me. How can i check the lag in that case ?
>
> Is your environment switching dynamically to async if the lag is too
> important? If not, once you have reached a sync state, the master
> would wait for all transactions commits to complete on the slave, so
> once the client has received a commit confirmation you have the
> guarantee that the data is already flushed on the slave. In this case
> you don't need to know what happens on the master.
> --
> Michael
>