Re: [GENERAL] How to define the limit length for numeric type?

[vod vos]

Sorry for taking varchar(4) as an example, What I want urgently is how to make 
a constraint of fixed length of a numeric data, 
that you can only input data like 23.45,  and if you input the data like 2.45, 
23.4356, 233.45, you will get a warning 
message from postgresql.

I think expr will do the job, but are there any simpler ways to do it in 
postgresql?


  On 星期日, 12 三月 2017 14:28:53 -0700 rob stone  wrote 
 
 > Hello, 
 >  
 > On Sat, 2017-03-11 at 22:14 -0800, vod vos wrote: 
 > > Hi everyone, 
 > >  
 > > How to define the exact limit length of numeric type? For example,  
 > >  
 > > CREATE TABLE test  (id serial, goose numeric(4,1)); 
 > >  
 > > 300.2 and 30.2 can be inserted into COLUMN goose, but I want 30.2 or 
 > > 3.2 can not be inserted, how to do this? 
 > >  
 > > Thank you. 
 > >  
 > >  
 > >  
 >  
 >  
 > Assuming that column goose may only contain values ranging from 100.0 
 > to 999.9, then a check constraint along the lines of:- 
 >  
 > goose > 99.9 and < 1000 
 >  
 > should do the trick. 
 >  
 > HTH, 
 > Rob 
 >  
 >  
 > --  
 > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 
 > To make changes to your subscription: 
 > http://www.postgresql.org/mailpref/pgsql-general 
 >



-- 
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] How to define the limit length for numeric type?

[Charles Clavadetscher]

Hello

> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of vod vos
> Sent: Montag, 13. März 2017 15:52
> To: rob stone 
> Cc: pgsql-general 
> Subject: Re: [GENERAL] How to define the limit length for numeric type?
> 
> Sorry for taking varchar(4) as an example, What I want urgently is how to 
> make a constraint of fixed length of a
> numeric data, that you can only input data like 23.45,  and if you input the 
> data like 2.45, 23.4356, 233.45, you
> will get a warning message from postgresql.
> 
> I think expr will do the job, but are there any simpler ways to do it in 
> postgresql?

Well, I don't think that you will find anything simpler than using a regexp in 
a check constraint, as Tom and I did suggest.

https://www.postgresql.org/message-id/15358.1489336741%40sss.pgh.pa.us
https://www.postgresql.org/message-id/040301d29b01%2443d71f50%24cb855df0%24%40swisspug.org

I have some trouble understanding what you find so complicated in that solution?

Bye
Charles

> 
> 
>   On 星期日, 12 三月 2017 14:28:53 -0700 rob stone  
> wrote   > Hello,  >  > On Sat, 2017-
> 03-11 at 22:14 -0800, vod vos wrote:
>  > > Hi everyone,
>  > >
>  > > How to define the exact limit length of numeric type? For example,  > >  
> > > CREATE TABLE test  (id serial,
> goose numeric(4,1));  > >  > > 300.2 and 30.2 can be inserted into COLUMN 
> goose, but I want 30.2 or  > > 3.2 can not
> be inserted, how to do this?
>  > >
>  > > Thank you.
>  > >
>  > >
>  > >
>  >
>  >
>  > Assuming that column goose may only contain values ranging from 100.0  > 
> to 999.9, then a check constraint along
> the lines of:-  >  > goose > 99.9 and < 1000  >  > should do the trick.
>  >
>  > HTH,
>  > Rob
>  >
>  >
>  > --
>  > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)  > To 
> make changes to your subscription:
>  > http://www.postgresql.org/mailpref/pgsql-general
>  >
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] How to define the limit length for numeric type?

[David G. Johnston]

On Mon, Mar 13, 2017 at 7:51 AM, vod vos  wrote:

> Sorry for taking varchar(4) as an example, What I want urgently is how to
> make a constraint of fixed length of a numeric data,
> that you can only input data like 23.45,  and if you input the data like
> 2.45, 23.4356, 233.45, you will get a warning
> message from postgresql.
>
> I think expr will do the job, but are there any simpler ways to do it in
> postgresql?
>

​Requiring a fixed length, and not an amount range is unusual.  That the
only way to do it is to consider the input as text and use a regular
expression is understandable.

David J.​

[GENERAL] createuser: How to specify a database to connect to

[Schmid Andreas]

Hi

I'm trying to add a new DB user with the following command from my client 
machine:
createuser -h my.host.name -U mysuperusername --pwprompt newusername

I'm getting the following message:
createuser: could not connect to database postgres: FATAL:  no pg_hba.conf 
entry for host "10.0.0.1", user "mysuperusername", database "postgres", SSL on

Now, it's true that our pg_hba.conf doesn't allow access to the postgres 
database. We did this intentionally, as usually no one needs to connect to this 
database.

So I tried to do
export PGDATABASE=sogis
before the createuser command. But no success. Does anyone know of another way 
to achieve what I'm trying?

I whish to do it with createuser rather than with the SQL command CREATE USER 
because this way I can avoid the password for the new user to show up anywhere 
in the history.

I'm on 9.2 on Ubuntu 14.04.

Thank you very much,
Andi


-- 
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] createuser: How to specify a database to connect to

[Guillaume Lelarge]

2017-03-13 16:29 GMT+01:00 Schmid Andreas :

> Hi
>
> I'm trying to add a new DB user with the following command from my client
> machine:
> createuser -h my.host.name -U mysuperusername --pwprompt newusername
>
> I'm getting the following message:
> createuser: could not connect to database postgres: FATAL:  no pg_hba.conf
> entry for host "10.0.0.1", user "mysuperusername", database "postgres", SSL
> on
>
> Now, it's true that our pg_hba.conf doesn't allow access to the postgres
> database. We did this intentionally, as usually no one needs to connect to
> this database.
>
> So I tried to do
> export PGDATABASE=sogis
> before the createuser command. But no success. Does anyone know of another
> way to achieve what I'm trying?
>
> I whish to do it with createuser rather than with the SQL command CREATE
> USER because this way I can avoid the password for the new user to show up
> anywhere in the history.
>
> I'm on 9.2 on Ubuntu 14.04.
>
>
You can't. The createuser.c code specifically targets the "postgres"
database, which surprises me. Anyway, the only other way to do it is to use
psql, something like: psql -c "CREATE USER..." -h ... your_database


-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: [GENERAL] createuser: How to specify a database to connect to

[Tom Lane]

Schmid Andreas  writes:
> I'm trying to add a new DB user with the following command from my client 
> machine:
> createuser -h my.host.name -U mysuperusername --pwprompt newusername

> I'm getting the following message:
> createuser: could not connect to database postgres: FATAL:  no pg_hba.conf 
> entry for host "10.0.0.1", user "mysuperusername", database "postgres", SSL on

> Now, it's true that our pg_hba.conf doesn't allow access to the postgres 
> database. We did this intentionally, as usually no one needs to connect to 
> this database.

That may have been intentional but it was still a bad decision; the entire
point of the postgres database is to have a default landing-place for
connections that don't need to connect to a specific database within
the cluster.

> So I tried to do
> export PGDATABASE=sogis
> before the createuser command. But no success. Does anyone know of another 
> way to achieve what I'm trying?

CREATE USER?

> I whish to do it with createuser rather than with the SQL command CREATE USER 
> because this way I can avoid the password for the new user to show up 
> anywhere in the history.

If by "history" you're worried about the server-side statement log, this
is merest fantasy: the createuser program is not magic, it just constructs
and sends a CREATE USER command for you.  You'd actually be more secure
using psql, where (if you're superuser) you could shut off log_statement
for your session first.

If by "history" you mean ~/.psql_history, you could turn that off (psql -n)
or to protect the password specifically, you could use psql's \password
command.

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] createuser: How to specify a database to connect to

[Adrian Klaver]

On 03/13/2017 08:44 AM, Guillaume Lelarge wrote:

2017-03-13 16:29 GMT+01:00 Schmid Andreas mailto:andreas.sch...@bd.so.ch>>:

Hi

I'm trying to add a new DB user with the following command from my
client machine:
createuser -h my.host.name  -U mysuperusername
--pwprompt newusername

I'm getting the following message:
createuser: could not connect to database postgres: FATAL:  no
pg_hba.conf entry for host "10.0.0.1", user "mysuperusername",
database "postgres", SSL on

Now, it's true that our pg_hba.conf doesn't allow access to the
postgres database. We did this intentionally, as usually no one
needs to connect to this database.

So I tried to do
export PGDATABASE=sogis
before the createuser command. But no success. Does anyone know of
another way to achieve what I'm trying?

I whish to do it with createuser rather than with the SQL command
CREATE USER because this way I can avoid the password for the new
user to show up anywhere in the history.

I'm on 9.2 on Ubuntu 14.04.


You can't. The createuser.c code specifically targets the "postgres"
database, which surprises me. Anyway, the only other way to do it is to
use psql, something like: psql -c "CREATE USER..." -h ... your_database


Unfortunately that stills leaves the password in the Postgres log which 
is what the OP is trying to avoid. The immediate solution would be to 
open the postgres database in pg_hba.conf. A longer term solution would 
be to file an issue and see if the code can be changed to allow 
specifying a database to createuser.





--
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com



--
Adrian Klaver
adrian.kla...@aklaver.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] createuser: How to specify a database to connect to

[Guillaume Lelarge]

2017-03-13 16:57 GMT+01:00 Adrian Klaver :

> On 03/13/2017 08:44 AM, Guillaume Lelarge wrote:
>
>> 2017-03-13 16:29 GMT+01:00 Schmid Andreas > >:
>>
>> Hi
>>
>> I'm trying to add a new DB user with the following command from my
>> client machine:
>> createuser -h my.host.name  -U mysuperusername
>> --pwprompt newusername
>>
>> I'm getting the following message:
>> createuser: could not connect to database postgres: FATAL:  no
>> pg_hba.conf entry for host "10.0.0.1", user "mysuperusername",
>> database "postgres", SSL on
>>
>> Now, it's true that our pg_hba.conf doesn't allow access to the
>> postgres database. We did this intentionally, as usually no one
>> needs to connect to this database.
>>
>> So I tried to do
>> export PGDATABASE=sogis
>> before the createuser command. But no success. Does anyone know of
>> another way to achieve what I'm trying?
>>
>> I whish to do it with createuser rather than with the SQL command
>> CREATE USER because this way I can avoid the password for the new
>> user to show up anywhere in the history.
>>
>> I'm on 9.2 on Ubuntu 14.04.
>>
>>
>> You can't. The createuser.c code specifically targets the "postgres"
>> database, which surprises me. Anyway, the only other way to do it is to
>> use psql, something like: psql -c "CREATE USER..." -h ... your_database
>>
>
> Unfortunately that stills leaves the password in the Postgres log which is
> what the OP is trying to avoid. The immediate solution would be to open the
> postgres database in pg_hba.conf. A longer term solution would be to file
> an issue and see if the code can be changed to allow specifying a database
> to createuser.
>
>
It's not very hard to do. But I really wonder why it's not already done. I
fear there was a good idea, but I fail to see which one :)


-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: [GENERAL] createuser: How to specify a database to connect to

[Adrian Klaver]

On 03/13/2017 08:52 AM, Tom Lane wrote:

Schmid Andreas  writes:

I'm trying to add a new DB user with the following command from my client 
machine:
createuser -h my.host.name -U mysuperusername --pwprompt newusername



I'm getting the following message:
createuser: could not connect to database postgres: FATAL:  no pg_hba.conf entry for host 
"10.0.0.1", user "mysuperusername", database "postgres", SSL on



Now, it's true that our pg_hba.conf doesn't allow access to the postgres 
database. We did this intentionally, as usually no one needs to connect to this 
database.


That may have been intentional but it was still a bad decision; the entire
point of the postgres database is to have a default landing-place for
connections that don't need to connect to a specific database within
the cluster.


So I tried to do
export PGDATABASE=sogis
before the createuser command. But no success. Does anyone know of another way 
to achieve what I'm trying?


CREATE USER?


I whish to do it with createuser rather than with the SQL command CREATE USER 
because this way I can avoid the password for the new user to show up anywhere 
in the history.


If by "history" you're worried about the server-side statement log, this
is merest fantasy: the createuser program is not magic, it just constructs
and sends a CREATE USER command for you.  You'd actually be more secure
using psql, where (if you're superuser) you could shut off log_statement
for your session first.


There is a difference though:

createuser:

postgres-2017-03-13 09:02:57.980 PDT-0LOG:  statement: CREATE ROLE 
dummy_user PASSWORD 'md5beb9541d2dcea94e091cf05f1f526d32' NOSUPERUSER 
NOCREATEDB NOCREATEROLE INHERIT LOGIN;


psql> CREATE USER:

postgres-2017-03-13 09:03:27.147 PDT-0LOG:  statement: create user 
dummy_user with login password '1234';




If by "history" you mean ~/.psql_history, you could turn that off (psql -n)
or to protect the password specifically, you could use psql's \password
command.

regards, tom lane





--
Adrian Klaver
adrian.kla...@aklaver.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] createuser: How to specify a database to connect to

[Tom Lane]

Guillaume Lelarge  writes:
> It's not very hard to do. But I really wonder why it's not already done. I
> fear there was a good idea, but I fail to see which one :)

The core reason why we haven't complicated createuser in that particular
direction is that createuser is only a convenience function for easy
cases.  There is not anything it could do for you that you can't do in
psql, and there are multiple cases that it doesn't attempt to handle
at all (some of the less-common options to CREATE USER, for instance).
I don't have any problem with "I decided to get rid of the postgres
database" being one of the unhandled cases.

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] createuser: How to specify a database to connect to

[Guillaume Lelarge]

2017-03-13 17:10 GMT+01:00 Tom Lane :

> Guillaume Lelarge  writes:
> > It's not very hard to do. But I really wonder why it's not already done.
> I
> > fear there was a good idea, but I fail to see which one :)
>
> The core reason why we haven't complicated createuser in that particular
> direction is that createuser is only a convenience function for easy
> cases.  There is not anything it could do for you that you can't do in
> psql, and there are multiple cases that it doesn't attempt to handle
> at all (some of the less-common options to CREATE USER, for instance).
> I don't have any problem with "I decided to get rid of the postgres
> database" being one of the unhandled cases.
>
>
Sure, I understand. That's fine with me. Thanks for the explanation.


-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: [GENERAL] createuser: How to specify a database to connect to

[Tom Lane]

Adrian Klaver  writes:
> On 03/13/2017 08:52 AM, Tom Lane wrote:
>> If by "history" you're worried about the server-side statement log, this
>> is merest fantasy: the createuser program is not magic, it just constructs
>> and sends a CREATE USER command for you.  You'd actually be more secure
>> using psql, where (if you're superuser) you could shut off log_statement
>> for your session first.

> There is a difference though:

> psql> CREATE USER:

> postgres-2017-03-13 09:03:27.147 PDT-0LOG:  statement: create user 
> dummy_user with login password '1234';

Well, what you're supposed to do is

postgres=# create user dummy_user;
postgres=# \password dummy_user
Enter new password: 
Enter it again: 
postgres=# 

which will result in sending something like

ALTER USER dummy_user PASSWORD 'md5c5e9567bc40082671d02c654260e0e09'

You can additionally protect that by wrapping it into one transaction
(if you have a setup where the momentary existence of the role without a
password would be problematic) and/or shutting off logging beforehand.

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] createuser: How to specify a database to connect to

[Adrian Klaver]

On 03/13/2017 09:19 AM, Tom Lane wrote:

Adrian Klaver  writes:

On 03/13/2017 08:52 AM, Tom Lane wrote:

If by "history" you're worried about the server-side statement log, this
is merest fantasy: the createuser program is not magic, it just constructs
and sends a CREATE USER command for you.  You'd actually be more secure
using psql, where (if you're superuser) you could shut off log_statement
for your session first.



There is a difference though:



psql> CREATE USER:



postgres-2017-03-13 09:03:27.147 PDT-0LOG:  statement: create user
dummy_user with login password '1234';


Well, what you're supposed to do is

postgres=# create user dummy_user;
postgres=# \password dummy_user
Enter new password:
Enter it again:
postgres=#

which will result in sending something like

ALTER USER dummy_user PASSWORD 'md5c5e9567bc40082671d02c654260e0e09'

You can additionally protect that by wrapping it into one transaction
(if you have a setup where the momentary existence of the role without a
password would be problematic) and/or shutting off logging beforehand.


Got it.



regards, tom lane




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


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

[GENERAL] DELETE and JOIN

[Alexander Farber]

Good evening,

In a 9.5 database I would like players to rate each other and save the
reviews in the table:

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)
);

while user names and IP addresses are saved in the other database:

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL,
..
);

However, before saving a review, I would like to delete all previous
reviews coming from the same IP in the past 24 hours:

CREATE OR REPLACE FUNCTION words_review_user(
in_uid integer,/* this user is being rated */
in_author integer,   /* by the in_author user */
in_nice integer,
in_review varchar
) RETURNS void AS
$func$
DECLARE
_author_rep integer;
_author_ip integer;
BEGIN

/* find the current IP address of the author */

SELECT  ip
INTO_author_ip
FROMwords_users
WHERE   uid = in_author;

/* try to prevent review fraud - how to improve this query please?
*/

DELETE  FROM words_reviews
WHERE   uid = in_uid
AND AGE(updated) < INTERVAL '1 day'
AND EXISTS (
SELECT 1
FROM words_reviews r INNER JOIN words_users u USING(uid)
WHERE u.ip = u._author_ip
AND r.author = in_author
);

UPDATE words_reviews set
author= in_author,
nice  = in_nice,
review= in_review,
updated   = CURRENT_TIMESTAMP
WHERE uid = in_uid AND author = in_author;

IF NOT FOUND THEN
INSERT INTO words_reviews (
author,
nice,
review,
updated
) VALUES (
in_author,
in_nice,
in_review,
CURRENT_TIMESTAMP
);
END IF;

END
$func$ LANGUAGE plpgsql;

I have the feeling that the _author_ip variable is not really necessary and
I could use some kind of "DELETE JOIN" here, but can not figure it out.

Please advise a better query if possible

Best regards
Alex

Re: [GENERAL] DELETE and JOIN

[Tom Lane]

Alexander Farber  writes:
> ...
> However, before saving a review, I would like to delete all previous
> reviews coming from the same IP in the past 24 hours:
> ...
> I have the feeling that the _author_ip variable is not really necessary and
> I could use some kind of "DELETE JOIN" here, but can not figure it out.

Sure, see the USING clause in DELETE.  Although your example seems a
bit confused, since you're not actually referring to _author_ip anywhere.
And if you meant "_author_ip" where you wrote "u._author_ip", that's in
a sub-SELECT, where you could just add a join to words_users without
needing any nonstandard DELETE syntax.

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] DELETE and JOIN

[Adrian Klaver]

On 03/13/2017 09:39 AM, Alexander Farber wrote:

Good evening,

In a 9.5 database I would like players to rate each other and save the
reviews in the table:

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)
);

while user names and IP addresses are saved in the other database:

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL,
..
);

However, before saving a review, I would like to delete all previous
reviews coming from the same IP in the past 24 hours:

CREATE OR REPLACE FUNCTION words_review_user(
in_uid integer,/* this user is being rated */
in_author integer,   /* by the in_author user */
in_nice integer,
in_review varchar
) RETURNS void AS
$func$
DECLARE
_author_rep integer;
_author_ip integer;
BEGIN

/* find the current IP address of the author */

SELECT  ip
INTO_author_ip
FROMwords_users
WHERE   uid = in_author;

/* try to prevent review fraud - how to improve this query
please? */

DELETE  FROM words_reviews
WHERE   uid = in_uid
AND AGE(updated) < INTERVAL '1 day'
AND EXISTS (
SELECT 1
FROM words_reviews r INNER JOIN words_users u USING(uid)
WHERE u.ip = u._author_ip
AND r.author = in_author
);

UPDATE words_reviews set
author= in_author,
nice  = in_nice,
review= in_review,
updated   = CURRENT_TIMESTAMP
WHERE uid = in_uid AND author = in_author;

IF NOT FOUND THEN
INSERT INTO words_reviews (
author,
nice,
review,
updated
) VALUES (
in_author,
in_nice,
in_review,
CURRENT_TIMESTAMP
);
END IF;

END
$func$ LANGUAGE plpgsql;

I have the feeling that the _author_ip variable is not really necessary
and I could use some kind of "DELETE JOIN" here, but can not figure it out.


The USING clause?:

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

"PostgreSQL lets you reference columns of other tables in the WHERE 
condition by specifying the other tables in the USING clause. For 
example, to delete all films produced by a given producer, one can do:


DELETE FROM films USING producers
  WHERE producer_id = producers.id AND producers.name = 'foo';
"



Please advise a better query if possible

Best regards
Alex



--
Adrian Klaver
adrian.kla...@aklaver.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] DELETE and JOIN

[David G. Johnston]

On Mon, Mar 13, 2017 at 9:39 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> Good evening,
>
> In a 9.5 database I would like players to rate each other and save the
> reviews in the table:
>
> 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)
> );
>
> while user names and IP addresses are saved in the other database:
>
> CREATE TABLE words_users (
> uid SERIAL PRIMARY KEY,
> ip inet NOT NULL,
> ..
> );​
>


> ​[...]
>  all previous reviews coming from the same IP in the past 24 hours:
>

​SELECT (uid, author)  -- locate reviews
FROM word_reviews
JOIN words_users USING (u_id)
WHERE u_id IN ( -- from each of the following users...
SELECT wu.u_id
FROM words_users wu
WHERE wu.ip = (SELECT wui.ip FROM words_users wui WHERE wui,uid = in_uid)
-- find all users sharing the ip address of this supplied user
)​
AND updated >= [...]  -- but only within the specified time period

David J.

[GENERAL] Large and Growing Group of Files

[John Iliffe]

Can anybody tell what these files are and what they do, and more importantly 
if they are needed?

This database has been in use since about 2012 on PostgreSQL 9.2 and is 
quite active.  (both read and insert/change).   There seems to be one group 
of these files a week, total 316 files as of today.  I have truncated the 
list because all the names are just 5 digit numbers, with every so often a 
_fsm or _vm extension.  They are all in a subdirectory called 
PG_9.2_201204301/16385.


[root@prod03 usr]# ls -al /usr/pgsql_tablespaces/PG_9.2_201204301/16385 | 
wc -l
316


.. tail end of list.
-rw--- 1 postgres postgres  16384 Feb 28 19:07 16479
-rw--- 1 postgres postgres  90112 Mar 13 14:11 16480
-rw--- 1 postgres postgres 114688 Mar 13 18:27 16481
-rw--- 1 postgres postgres  98304 Mar 13 14:11 16482
-rw--- 1 postgres postgres  81920 Mar 13 14:11 16483
-rw--- 1 postgres postgres  90112 Mar 13 14:11 16484
-rw--- 1 postgres postgres  32768 Jun 10  2016 16485
-rw--- 1 postgres postgres  57344 Sep 22  2015 16486
-rw--- 1 postgres postgres   8192 Feb 24 17:00 16527
-rw--- 1 postgres postgres  16384 Feb 26 17:36 16529
-rw--- 1 postgres postgres  24576 Apr  4  2016 16529_fsm
-rw--- 1 postgres postgres   8192 Dec 29  2013 16529_vm
-rw--- 1 postgres postgres  16384 Feb 24 17:00 16533
-rw--- 1 postgres postgres512 Jan  6  2013 pg_filenode.map
-rw--- 1 postgres postgres 111220 Jan 17 19:33 pg_internal.init
-rw--- 1 postgres postgres  4 Jan  6  2013 PG_VERSION



I have never looked in depth at the database files before; just did it now 
because I'm working on building a new server!

Regards, 

John
==



-- 
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] Large and Growing Group of Files

[Tom Lane]

John Iliffe  writes:
> Can anybody tell what these files are and what they do, and more importantly 
> if they are needed?

They are database table files, and you will certainly be unhappy if you
just manually rm them.

You can read some theory here:

https://www.postgresql.org/docs/9.2/static/storage.html

and for help in identifying specific files you might like oid2name:

https://www.postgresql.org/docs/9.2/static/oid2name.html

although it's certainly possible to get the same results with manual
queries on the system catalogs.

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] Large and Growing Group of Files

[David G. Johnston]

On Mon, Mar 13, 2017 at 4:12 PM, Tom Lane  wrote:

> John Iliffe  writes:
> > Can anybody tell what these files are and what they do, and more
> importantly
> > if they are needed?
>
> They are database table files, and you will certainly be unhappy if you
> just manually rm them.
>
> You can read some theory here:
>
> https://www.postgresql.org/docs/9.2/static/storage.html


​In particular note the paragraph whose leading sentence is:

"​Tablespaces make the scenario more complicated."

What you showed up was the "far end" of a custom tablespace link.

David J.

Re: [GENERAL] Large and Growing Group of Files

[John Iliffe]

On Monday 13 March 2017 19:12:10 Tom Lane wrote:
> John Iliffe  writes:
> > Can anybody tell what these files are and what they do, and more
> > importantly if they are needed?
> 
> They are database table files, and you will certainly be unhappy if you
> just manually rm them.
> 
> You can read some theory here:
> 
> https://www.postgresql.org/docs/9.2/static/storage.html
> 
> and for help in identifying specific files you might like oid2name:
> 
> https://www.postgresql.org/docs/9.2/static/oid2name.html
> 
> although it's certainly possible to get the same results with manual
> queries on the system catalogs.
> 
>   regards, tom lane
Thanks Tom.

I was sure they weren't abandoned but the increasing number of them made me 
wonder if I had a configuration issue.

John


-- 
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] Recovery damaged dump file

[mac pack]

2017-03-10 14:58 GMT+00:00 Adrian Klaver :

> On 03/10/2017 01:25 AM, mac pack wrote:
>
>>
>> 2017-03-10 5:11 GMT+00:00 Adrian Klaver > >:
>>
>>
>> On 03/09/2017 09:09 PM, Adrian Klaver wrote:
>>
>> On 03/09/2017 03:55 AM, mac pack wrote:
>>
>> Hi.
>>
>> My PostgreSQL server was affect by a Ransomware virus. I'm
>> trying to
>> restore the database from a dump file made by pg_dump in
>> custom format
>> (-F c option), but the dump file seems to be damaged in the
>> first's 1000
>> lines.
>>
>> Opening the file with vi shows ^@^@^@^@^@^@^@^@^@^@^
>> followed by part of
>> the databse schema and the a lote of lines with binary
>> characters that i
>> think is the table's data.
>>
>>
>> The custom format is a binary format so non text characters would
>> be
>> expected.
>>
>> Did you try to restore using the file, before doing the below?
>>
>>
>> yes
>>
>>
>> If there was an error when you did that and if so what was it?
>>
>>
>> Should be:
>>
>> Was there an error when you did that and if so what was it?
>>
>>
>> pg_restore db.bckup > out.sql
>> pg_restore: [archiver] input file does not appear to be a valid archive
>>
>> Opening the original damaged file in vi with :%!xxd it show's:
>>
>
> So something zeroed out the beginning of the file.
>
> I know you said:
>
> pg_restore  -s db.backup > out.sql
>
> works. Does the opposite work also?:
>
> pg_restore  -a db.backup > out.sql
>

No.
pg_restore: [custom archiver] unrecognized data block type (0) while
searching archive



>
> I would say start with Michael's suggestion of working through object by
> object.
>
>
It gives me the same error, unrecognized data block type (0) while
searching archive



> You mentioned another dev database, how close is that to the content of
> the damaged database?
>

The schema is the same, but the data is different.

Any one knows how pg_dump saves the dump in custom format. What is the
structure of the format, there is any doc about that?

Thanks.



>
>
>
>> 0003ff80:          
>> 0003ff90:          
>> 0003ffa0:          
>> 0003ffb0:          
>> 0003ffc0:          
>> 0003ffd0:          
>> 0003ffe0:          
>> 0003fff0:          
>> 0004: 3331 3820 2020 2020 2020 2020 2020 202d  318-
>> 00040010: 272c 206e 756c 6c29 3b0d 0a69 6e73 6572  ', null);..inser
>> 00040020: 7420 696e 746f 206d 6f72 6164 6173 6374  t into moradasct
>> 00040030: 7428 6964 5f64 6973 7463 6f6e 632c 206c  t(id_distconc, l
>> 00040040: 6f63 616c 6964 6164 652c 2061 7272 7561  ocalidade, arrua
>> 00040050: 6d65 6e74 6f2c 2074 726f 636f 2c20 6c69  mento, troco, li
>> 00040060: 6d5f 696e 6665 7269 6f72 5f70 6f72 7461  m_inferior_porta
>> 00040070: 2c20 636c 6965 6e74 652c 2063 6f64 6967  , cliente, codig
>> 00040080: 6f5f 706f 7374 616c 2c20 6c69 6d5f 7375  o_postal, lim_su
>> 00040090: 7065 7269 6f72 2920 7661 6c75 6573 2028  perior) values (
>>
>>
>>
>>
>>
>>
>> I tried to replace those first lines with lines from other
>> dev database
>> and i can run pg_restore but at some point throws error.
>>
>> pg_restore  db.backup > out.sql
>>
>> pg_restore: [custom archiver] unrecognized data block type
>> (0) while
>> searching archive
>>
>> The follow commands works fine:
>> pg_restore  -s db.backup > out.sql
>> pg_restore -l db.backup
>>
>> Do you think it's possible to recover the dump file, is
>> there any method
>> or tool to recover dump files?
>>
>>
>> Thanks.
>> Mário
>>
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

[GENERAL] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

[Raffaele Irlanda]

Hello People of PostgreSQL,

Unfortunately only nowadays (March 2017), and only by chance I spotted this
2010 discussion during a Google search.

Name of discussion:

Wikipedia entry - AmigaOS port - error?

id of message:

https://www.postgresql.org/message-id/839805.58072.qm%40web23603.mail.ird.yahoo.com

I found strange the doubt Mr. Glyn Astill expressed that Amiga platform
entry in Wikipedia about availability of PostgreSQL could be an error, as
he was just unaware that platform had a porting of PostgreSQL even if
unofficial...

Also I found strange the fact Mr. Tom Lane immediately deleted Amiga
reference entry on Wikipedia in article regarding RDBMS without performing
any serious search on Google, lasting more than a couple of minutes of true
serious search, or maybe asking directly into Amiga Forums about PostgreSQL
to Amiga Communituy of Users.

Infacts there is a port of PostgreSQL made in the late 2005 (five years
before your discussion started) present in online Amiga Repository AMINET
and it was available for the Amiga clone Operating System called MorphOS

http://aminet.net/package/dev/gg/postgresql632-mos-bin

In 2010 it has been sure completely outdated but sure you can see proof it
exists, and that Amiga had full dignity of being present into Wikipedia
Article.

A simple Google search could had helped you and sure it had solved your
doubts, or maybe just paying a visit on Amiga Forums that are well
represented online and asking directly to members of Amiga Community that
are well known for their friendly welcome attitude (I can assure you two
that we don't bite people from other platforms) sure had saved Mr. Tom Lane
from his presupponential deletion of Amiga Entry in RDBMS in Wikipedia.

I get this occasion to inform you gentlemen that there are also various
packages regarding SQLite into Amiga repositories, if you search for it.
Last available package is from 2016, so it is not outdated this time. and
If you are active also in SQLite Community now you have full info about
Amiga portings.

http://aminet.net/search?query=SQLite

Hope from now on future, you will not delete anymore -abruptly- Amiga
entries before informing yourself about Amiga portings availability when
encoutering this platform name by chance on Wikipedia or other sites.

With respect,

Raffaele irlanda

Re: [GENERAL] Recovery damaged dump file

[Michael Paquier]

On Mon, Mar 13, 2017 at 7:40 PM, mac pack  wrote:
> Any one knows how pg_dump saves the dump in custom format. What is the
> structure of the format, there is any doc about that?

There are no docs, but the code in src/bin/pg_dump/pg_backup_custom.c
can speak by itself if you are willing to know what each byte is
allocated for.
-- 
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] general erros backup

[Adrian Klaver]

On 03/12/2017 11:30 AM, sttefaniribe...@yahoo.com.br wrote:





I can’t resolve this problema with backup configuration. Can you help me?


Not really as I see no useful error message.

What happens if you run the command in a terminal?





Enviado do Email  para
Windows 10









--
Adrian Klaver
adrian.kla...@aklaver.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] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

[Adrian Klaver]

On 03/13/2017 02:13 AM, Raffaele Irlanda wrote:

Hello People of PostgreSQL,

Unfortunately only nowadays (March 2017), and only by chance I spotted
this 2010 discussion during a Google search.

Name of discussion:

Wikipedia entry - AmigaOS port - error?

id of message:

https://www.postgresql.org/message-id/839805.58072.qm%40web23603.mail.ird.yahoo.com

I found strange the doubt Mr. Glyn Astill expressed that Amiga platform
entry in Wikipedia about availability of PostgreSQL could be an error,
as he was just unaware that platform had a porting of PostgreSQL even if
unofficial...

Also I found strange the fact Mr. Tom Lane immediately deleted Amiga
reference entry on Wikipedia in article regarding RDBMS without
performing any serious search on Google, lasting more than a couple of
minutes of true serious search, or maybe asking directly into Amiga
Forums about PostgreSQL to Amiga Communituy of Users.

Infacts there is a port of PostgreSQL made in the late 2005 (five years
before your discussion started) present in online Amiga Repository
AMINET and it was available for the Amiga clone Operating System called
MorphOS




http://aminet.net/package/dev/gg/postgresql632-mos-bin

In 2010 it has been sure completely outdated but sure you can see proof
it exists, and that Amiga had full dignity of being present into
Wikipedia Article.


I am pretty sure it is:

https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Operating_system_support

PostgreSQL  Yes Yes Yes Yes Yes Yes (MorphOS)[24]



A simple Google search could had helped you and sure it had solved your
doubts, or maybe just paying a visit on Amiga Forums that are well
represented online and asking directly to members of Amiga Community
that are well known for their friendly welcome attitude (I can assure
you two that we don't bite people from other platforms) sure had saved
Mr. Tom Lane from his presupponential deletion of Amiga Entry in RDBMS
in Wikipedia.

I get this occasion to inform you gentlemen that there are also various
packages regarding SQLite into Amiga repositories, if you search for it.
Last available package is from 2016, so it is not outdated this time.
and If you are active also in SQLite Community now you have full info
about Amiga portings.

http://aminet.net/search?query=SQLite

Hope from now on future, you will not delete anymore -abruptly- Amiga
entries before informing yourself about Amiga portings availability when
encoutering this platform name by chance on Wikipedia or other sites.

With respect,

Raffaele irlanda



--
Adrian Klaver
adrian.kla...@aklaver.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] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

[Scott Marlowe]

On Mon, Mar 13, 2017 at 10:41 PM, Adrian Klaver
 wrote:
> On 03/13/2017 02:13 AM, Raffaele Irlanda wrote:

>> http://aminet.net/package/dev/gg/postgresql632-mos-bin
>>
>> In 2010 it has been sure completely outdated but sure you can see proof
>> it exists, and that Amiga had full dignity of being present into
>> Wikipedia Article.
>
>
> I am pretty sure it is:
>
> https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Operating_system_support
>
> PostgreSQL  Yes Yes Yes Yes Yes Yes (MorphOS)[24]
>

Oh man, 2005's versuin 6.3.2.  It's more of a historical oddity than a
version I'd ever wanna run. Isn't there some way to get gnuc etc
running on an amiga and then work on direct support of the platform if
that's what you want? I'd think paying a well known amiga hacker to
port it would be a worthwhile investment.


-- 
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] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

[Tom Lane]

Scott Marlowe  writes:
> On Mon, Mar 13, 2017 at 10:41 PM, Adrian Klaver
>  wrote:
>> I am pretty sure it is:
>> https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Operating_system_support
>> PostgreSQL  Yes Yes Yes Yes Yes Yes (MorphOS)[24]

> Oh man, 2005's versuin 6.3.2.  It's more of a historical oddity than a
> version I'd ever wanna run.

More to the point, whatever "MorphOS" is, it isn't AmigaOS.  It was pretty
clearly stated in the original thread that AmigaOS had no support for
fork(), without which there was no chance of running Postgres.  It's not
unreasonable of us to take the position that AmigaOS isn't a supported
platform, because *it is not*, and never has been.

(And, for the record, I didn't edit that wikipedia entry.  I don't even
have a wikipedia account.)

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] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

[John R Pierce]

On 3/13/2017 10:06 PM, Scott Marlowe wrote:

I am pretty sure it is:

https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Operating_system_support

PostgreSQL  Yes Yes Yes Yes Yes Yes (MorphOS)[24]


Oh man, 2005's versuin 6.3.2.  It's more of a historical oddity than a
version I'd ever wanna run. Isn't there some way to get gnuc etc
running on an amiga and then work on direct support of the platform if
that's what you want? I'd think paying a well known amiga hacker to
port it would be a worthwhile investment.



indeed, a 12 year old one-time port to an obscure platform which is at 
best a historical oddity does no one any favors.  if its not supported 
and reasonably current, it might as well not exist.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] PostgreSQL general discussions list - 2010 Thread: Wikipedia entry - AmigaOS port - error?

[Scott Marlowe]

On Mon, Mar 13, 2017 at 11:15 PM, John R Pierce  wrote:
> On 3/13/2017 10:06 PM, Scott Marlowe wrote:
>
> I am pretty sure it is:
>
> https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Operating_system_support
>
> PostgreSQL  Yes Yes Yes Yes Yes Yes (MorphOS)[24]
>
> Oh man, 2005's versuin 6.3.2.  It's more of a historical oddity than a

Correction 1998's.

> version I'd ever wanna run. Isn't there some way to get gnuc etc
> running on an amiga and then work on direct support of the platform if
> that's what you want? I'd think paying a well known amiga hacker to
> port it would be a worthwhile investment.
>
>
>
> indeed, a 12 year old one-time port to an obscure platform which is at best
> a historical oddity does no one any favors.  if its not supported and
> reasonably current, it might as well not exist.
>
>
> --
> john r pierce, recycling bits in santa cruz



-- 
To understand recursion, one must first understand recursion.


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