Re: [GENERAL] [HACKERS] Change in order of criteria - reg

[Amit Langote]

On 2016/06/01 13:07, sri harsha wrote:
> Hi,
> 
> In PostgreSQL , does the order in which the criteria is given matter ??
> For example
> 
> Query 1 : Select * from TABLE where a > 5 and b < 10;
> 
> Query 2 : Select * from TABLE where b <10 and a > 5;
> 
> Are query 1 and query 2 the same in PostgreSQL or different ?? If its
> different , WHY ??

tl;dr they are the same.  As in they obviously produce the same result and
result in invoking the same plan.

Internally, optimizer will order application of those quals in resulting
plan based on per-tuple cost of individual quals.  So a cheaper, more
selective qual might result in short-circuiting of relatively expensive
quals for a large number of rows in the table saving some cost in
run-time.  Also, if index scan is chosen and quals pushed down, the
underlying index method might know to order quals smartly.

However, the cost-markings of operators/functions involved in quals better
match reality.  By default, most operators/functions in a database are
marked with cost of 1 unit.  Stable sorting used in ordering of quals
would mean the order of applying quals in resulting plan matches the
original order (ie, the order in which they appear in the query).  So, if
the first specified qual really happens to be an expensive qual but marked
as having the same cost as other less expensive quals, one would have to
pay the price of evaluating it for all the rows.  Whereas, correctly
marking the costs could have avoided that (as explained above).  Note that
I am not suggesting that ordering quals in query by their perceived cost
is the solution.  Keep optimizer informed by setting costs appropriately
and it will do the right thing more often than not. :)

Thanks,
Amit




-- 
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] postgres_fdw and Kerberos authentication

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Jean-Marc Lessard  writes:
> > A nice way to meet security requirements would be to provide single sign on 
> > support for the postgres_fdw.
> > As long as you have defined a user in the source and destination databases, 
> > and configure the Kerberos authentication you should be able to use 
> > postgres_fdw.
> 
> It's not really that easy, because postgres_fdw (like the server in
> general) is running as the database-owner operating system user.
> How will you associate a Postgres role that's responsible for a
> particular connection request with some Kerberos credentials,
> while keeping it away from credentials that belong to other roles?

That's actually not that difficult and is something which Apache and
mod_auth_kerb has been doing for a very long time.

> This is certainly something that'd be useful to have, but it's not
> clear how to do it in a secure fashion.

The database owner operating system user has to be trusted, along with
any superusers in the database, but if you assume those, then having PG
manage the different Kerberos cache files (one for each backend which
has authenticated via Kerberos and passed through delegation
credentials) should work.  Clearly, we can't give the user control over
which credential cache to use.

Having to trust the OS user and superusers with those credentials isn't
any different from using passwords with postgres_fdw.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

[CN]

On Tue, May 31, 2016, at 10:20 PM, Tom Lane wrote:
> There's also a bunch of issues having to do with the fact that the
> semantics of SET SESSION AUTHORIZATION are defined by the SQL standard
> and don't exactly match what you'd want, in many cases, for "become
> this other role".  Some of them include
> * You retain the original login role's abilities to issue SET SESSION
> AUTHORIZATION, either back to itself or to a third role.
> * You can also get back to the original role with DISCARD ALL.
> * Any session-level settings specified for the new role with ALTER
> USER SET don't get adopted.
> While you could imagine that specific applications might be okay with
> these things, they're pretty fatal for a general-purpose connection
> pooler; the first two in particular would be unacceptable security
> holes.

I understand most of your viewpoints.
Perhaps I should pay more attention to general purpose connection
spoolers.


Below comments are for those who are still interested in this topic.

My intention is to minimize the number of connections and re-use them,
and to contain each role into one distinct schema.
The following test results indicate that I am unlikely to get what I
want from these commands:

SET ROLES
SET SESSION AUTHORIZATION
GRANT
REVOKE

First, connect to server with superuser.

postgres=# create role r1;
CREATE ROLE
postgres=# create schema s1;
CREATE SCHEMA
postgres=# grant all on schema s1 to r1;
GRANT
postgres=# revoke all on schema public from r1;
REVOKE
postgres=# set role r1;
SET
postgres=> create table t1 (c1 text);
CREATE TABLE
postgres=> \dn
  List of schemas
  Name  |  Owner   
+--
 public | postgres
 s1 | postgres
(2 rows)

postgres=> \d
   List of relations
 Schema | Name | Type  | Owner 
+--+---+---
 public | t1   | table | r1
(1 row)

As shown above, table "t1" gets created in schema "public" while my
intention is to keep role "r1" and  all its objects out of schema
"public".

Sure I can issue command "SET SEARCH_PATH TO s1" before command "CREATE
table". However, the problem with such arrangement is that role "r1" can
create in schema "s1" those commands like "SET ROLE r2" or "SET
SEARCH_PATH TO s2" and therefore create or access objects not belonging
to itself once these commands get executed.

Best Regards,
CN

-- 
http://www.fastmail.com - IMAP accessible web-mail



-- 
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] Switching roles as an replacement of connection pooling tools

[CN]

On Tue, May 31, 2016, at 10:23 PM, Melvin Davidson wrote:
> That being said, IMHO, I believe having a separate schema for every
> user is poor database design
 
This is the best arrangement I can think of now for my use case:
 
1. Each schema holds an  application independent from other schemas
   within the same database.
2. Each schema is fully controlled by a role who is responsible for the
   development of that application.
 
Best Regards,
CN

-- 
http://www.fastmail.com - The professional email service

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

[David G. Johnston]

On Wed, Jun 1, 2016 at 8:59 AM, CN  wrote:

> On Tue, May 31, 2016, at 10:20 PM, Tom Lane wrote:
> > There's also a bunch of issues having to do with the fact that the
> > semantics of SET SESSION AUTHORIZATION are defined by the SQL standard
> > and don't exactly match what you'd want, in many cases, for "become
> > this other role".  Some of them include
> > * You retain the original login role's abilities to issue SET SESSION
> > AUTHORIZATION, either back to itself or to a third role.
> > * You can also get back to the original role with DISCARD ALL.
> > * Any session-level settings specified for the new role with ALTER
> > USER SET don't get adopted.
> > While you could imagine that specific applications might be okay with
> > these things, they're pretty fatal for a general-purpose connection
> > pooler; the first two in particular would be unacceptable security
> > holes.
> ​[...]​
>
> First, connect to server with superuser.
> ​[...]
>
> Sure I can issue command "SET SEARCH_PATH TO s1" before command "CREATE
> table". However, the problem with such arrangement is that role "r1" can
> create in schema "s1" those commands like "SET ROLE r2" or "SET
> SEARCH_PATH TO s2" and therefore create or access objects not belonging
> to itself once these commands get executed.
>

​Would a scheme whereby you basically only get to SET ROLE one time work?
Basically the connection layer logs in and immediately SET SESSION
AUTHORIZATION AND SET ROLE [WITH SETTINGS?] to another role.  For all
intents and purposes the session now looks as if that role was the one that
performed the login.  However, that role is forbidden from changing its
identity.  This removes attack vectors but also means that applications
cannot made use of finer grained grants without the main role inheriting
all of them.  I can see this being an acceptable trade-off in some/many
uses.

When the session is returned to a pool it can either be kept around waiting
for another request by the same user or it would have to be closed and
reestablished should the connection need to be freed up for another user.

You'd still have to make sure that the user that can invoke arbitrary SQL
commands can only get locked sessions from some central authority.  This
seems problematic.  In most co-tenant setups the tenants are simply
forbidden from executing arbitrary SQL and the SET ROLE is more for utility
than security.  You are trusting that the piece of software that can
execute SQL.

​David J.​

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

[Tom Lane]

"David G. Johnston"  writes:
> ​Would a scheme whereby you basically only get to SET ROLE one time work?
> Basically the connection layer logs in and immediately SET SESSION
> AUTHORIZATION AND SET ROLE [WITH SETTINGS?] to another role.  For all
> intents and purposes the session now looks as if that role was the one that
> performed the login.  However, that role is forbidden from changing its
> identity.  This removes attack vectors but also means that applications
> cannot made use of finer grained grants without the main role inheriting
> all of them.  I can see this being an acceptable trade-off in some/many
> uses.

> When the session is returned to a pool it can either be kept around waiting
> for another request by the same user or it would have to be closed and
> reestablished should the connection need to be freed up for another user.

Doesn't seem like this'd actually provide any useful functionality for a
connection pooler.  It still has to restrict any one underlying connection
to be used by only one role.  You've added more bookkeeping (because
there's a state where a connection's role is unassigned) but no
flexibility.

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] Switching roles as an replacement of connection pooling tools

[David G. Johnston]

On Wed, Jun 1, 2016 at 9:56 AM, Tom Lane  wrote:

> "David G. Johnston"  writes:
> > ​Would a scheme whereby you basically only get to SET ROLE one time work?
> > Basically the connection layer logs in and immediately SET SESSION
> > AUTHORIZATION AND SET ROLE [WITH SETTINGS?] to another role.  For all
> > intents and purposes the session now looks as if that role was the one
> that
> > performed the login.  However, that role is forbidden from changing its
> > identity.  This removes attack vectors but also means that applications
> > cannot made use of finer grained grants without the main role inheriting
> > all of them.  I can see this being an acceptable trade-off in some/many
> > uses.
>
> > When the session is returned to a pool it can either be kept around
> waiting
> > for another request by the same user or it would have to be closed and
> > reestablished should the connection need to be freed up for another user.
>
> Doesn't seem like this'd actually provide any useful functionality for a
> connection pooler.  It still has to restrict any one underlying connection
> to be used by only one role.  You've added more bookkeeping (because
> there's a state where a connection's role is unassigned) but no
> flexibility.
>

Y
​ou basically delegate authentication for users to the pooler.  Only the
pooler needs to be setup with credentials and given access via
pg_hba.conf.  Though pg_hba isn't really a problem, you'd just allow
everyone in from the same machine.​  So mostly its about not have
credentials and giving the pooler some level of play with managing
resources.

​Every connection would have a role assigned just like it does today.  The
difference is that now certain roles will have an added attribute
forbidding them from being SET ROLEd away from. I'm sure there is a lot
more to it than that but the concept of a one-way switch should remove many
of the hazards present today.  The point of the password was to continue to
allow multiple-switching - but securely.  That seems to be much more
complicated setup to implement and so the question should be asked whether
that capability is even necessary.

I'll admit that the trade-offs are not appealing for large scale but
resource constrained setups but I suspect for those setups sharding is
going to end up being the area of attention; not delegation/proxy.

David J.

Re: [GENERAL] Row security policies documentation question

[Alexander M. Sauer-Budge]

> On May 31, 2016, at 5:16 PM, David G. Johnston  
> wrote:
> 
> On Tue, May 31, 2016 at 4:59 PM, Alexander M. Sauer-Budge 
> mailto:ambu...@alum.mit.edu>> wrote:
> Hello,
> 
> Section 5.7. on Row Security Policies 
> (https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html 
> ) for 
> 9.5 says:
>  
> [...]
>  
> ​
> CREATE POLICY user_policy ON users
> USING (user = current_user);
> 
> ---
> Is this a table column in a `users` table the example doesn’t define or does 
> PostgreSQL keep track of what user/role inserted a row and allow policies to 
> use it?
> 
> ​It assumes the user can envision a trivial "users" table having at least a 
> column named "user" that represents the user's name/id and which the names of 
> said users are identical to those assigned to them in the PostgreSQL database 
> and accessible via the "pg_authid" catalog (rolname) and its related views: 
> namely "pg_user" (usename).
> 
> ​​So, in effect the following works, and returns a single row.
> 
> SELECT *
> FROM users
> JOIN pg_user ON (user = usename)
> WHERE user = current_user;

Thanks David! I appreciate the clarification and the extra context. So if I 
wanted to establish a “row owner role” and only permit that role or any other 
role with direct or inherited membership in that role to access the row, then 
I’d do something explicit like this:


CREATE TABLE mytable (id integer, value text, owner_role text);
ALTER TABLE mytable ENABLE ROW LEVEL SECURITY;
CREATE POLICY mytable_policy ON mytable USING (pg_has_role(current_user, 
owner_role, 'member'));
CREATE ROLE mygroup NOLOGIN;
GRANT ALL ON mytable TO mygroup;
CREATE ROLE myuser NOLOGIN;
GRANT mygroup TO myuser;

SET ROLE mygroup;
INSERT INTO mytable VALUES (1, 'test value 1’, current_user);

SET ROLE myuser;
SELECT * FROM mytable;
 id | value | owner_role
+---+
  1 | test value 1  | mygroup
(1 row)

RESET ROLE;
CREATE ROLE anotheruser NOLOGIN;
GRANT ALL ON mytable TO anotheruser;
SET ROLE anotheruser;
SELECT * FROM mytable;
 id | value | owner_role
+---+
(0 rows)


Is this the most direct and performant way to use row security to establish a 
permission system that behaves similarly to table/column permissions?

Thanks!
Alex

Re: [GENERAL] Row security policies documentation question

[Alexander M. Sauer-Budge]

> On May 31, 2016, at 7:48 PM, Adrian Klaver  wrote:
> 
> For a good review of what is possible with RLS take a look at this blog:
> 
> http://blog.2ndquadrant.com/application-users-vs-row-level-security/ 
> 

Fantastic! Thanks!

Re: [GENERAL] Checkpoint Err on Startup of Rsynced System

[Jeff Janes]

On Tue, May 31, 2016 at 10:13 AM, Jim Longwill  wrote:
> I am trying to setup a 2nd, identical, db server (M2) for development and
> I've run into a problem with starting up the 2nd Postgres installation.
>
> Here's what I've done:
>   1) did a 'clone' of 1st (production) machine M1 (so both machines on Cent
> OS 7.2)
>   2) setup an rsync operation, did a complete 'rsync' from M1 to M2
>   3) did a final 'CHECKPOINT' command on M1 postgres
>   4) shutdown postgres on M1 with 'pg_ctl stop'
>   5) did final 'rsync' operation  (then restarted postgres on M1 with
> 'pg_ctl start')
>   6) tried to startup postgres on M2
>
> It won't start, & in the log file gives the error message:
> ...
> < 2016-05-31 09:02:52.337 PDT >LOG:  invalid primary checkpoint record
> < 2016-05-31 09:02:52.337 PDT >LOG:  invalid secondary checkpoint record
> < 2016-05-31 09:02:52.337 PDT >PANIC:  could not locate a valid checkpoint
> record
> < 2016-05-31 09:02:53.184 PDT >LOG:  startup process (PID 26680) was
> terminated by signal 6: Aborted
> < 2016-05-31 09:02:53.184 PDT >LOG:  aborting startup due to startup process
> failure
>
> I've tried several times to do this but always get this result.  So, do I
> need to do a new 'initdb..' operation on machine M2 + restore from M1
> backups?  Or is there another way to fix this?


It sounds like you did not include pg_xlog in your rsync.  What you
have done is basically a cold backup.  Cold backups must include
pg_xlog, at least if you want them to work without WAL archival.  If
you look farther up in the log, it should tell you what xlog file it
needs, and you can copy that from M1 if it is still there.

Cheers,

Jeff


-- 
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] Checkpoint Err on Startup of Rsynced System

[Jim Longwill]

Jeff Janes,

Ok.  I checked this further and just found that the pg_xlog area is 
symlinked to another area.. and indeed that other area was not being 
rsynced (!) and I thought it was.  So, I just fixed this, re-ran it and 
now it is working.  Now I believe I have a stable postgres running on M2.


So, thanks Jeff for mentioning pg_xlog.  Thanks to others as well for 
your input.


--Jim Longwill

On 06/01/2016 08:44 AM, Jeff Janes wrote:

On Tue, May 31, 2016 at 10:13 AM, Jim Longwill  wrote:

I am trying to setup a 2nd, identical, db server (M2) for development and
I've run into a problem with starting up the 2nd Postgres installation.

Here's what I've done:
   1) did a 'clone' of 1st (production) machine M1 (so both machines on Cent
OS 7.2)
   2) setup an rsync operation, did a complete 'rsync' from M1 to M2
   3) did a final 'CHECKPOINT' command on M1 postgres
   4) shutdown postgres on M1 with 'pg_ctl stop'
   5) did final 'rsync' operation  (then restarted postgres on M1 with
'pg_ctl start')
   6) tried to startup postgres on M2

It won't start, & in the log file gives the error message:
...
< 2016-05-31 09:02:52.337 PDT >LOG:  invalid primary checkpoint record
< 2016-05-31 09:02:52.337 PDT >LOG:  invalid secondary checkpoint record
< 2016-05-31 09:02:52.337 PDT >PANIC:  could not locate a valid checkpoint
record
< 2016-05-31 09:02:53.184 PDT >LOG:  startup process (PID 26680) was
terminated by signal 6: Aborted
< 2016-05-31 09:02:53.184 PDT >LOG:  aborting startup due to startup process
failure

I've tried several times to do this but always get this result.  So, do I
need to do a new 'initdb..' operation on machine M2 + restore from M1
backups?  Or is there another way to fix this?


It sounds like you did not include pg_xlog in your rsync.  What you
have done is basically a cold backup.  Cold backups must include
pg_xlog, at least if you want them to work without WAL archival.  If
you look farther up in the log, it should tell you what xlog file it
needs, and you can copy that from M1 if it is still there.

Cheers,

Jeff


--
--o--o--o--o--o--o--o--o--o--o--o--o--
Jim Longwill
PSMFC Regional Mark Processing Center
Ph:503-595-3146; FAX:503-595-3446
jlongw...@psmfc.org
--o--o--o--o--o--o--o--o--o--o--o--o--



--
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] Slides for PGCon2016; "FTS is dead ? Long live FTS !"

[Kaare Rasmussen]

On 2016-05-31 13:24, Stefan Keller wrote:


> We chose RUM just because there are GIN and VODKA :)
> But some people already suggested several meanings like Really 
Useful iMdex :)

> We are open for suggestion.

So I propose: "Ranking UMdex" ;-)



How about "Russian Unbelievable Magic"? Or just "RUssian Magic" if you 
do believe...


/kaare


--
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] postgres_fdw and Kerberos authentication

[Jean-Marc Lessard]

Stephen Frost [sfr...@snowman.net]  wrote:
> The database owner operating system user has to be trusted, along with any 
> superusers in the database, but if you assume those, then having PG manage 
> the different Kerberos cache files
> (one for each backend which has authenticated via Kerberos and passed through 
> delegation credentials) should work.
> Clearly, we can't give the user control over which credential cache to use.

True, in such a case (single sign on) the user should not specify a user in the 
user mapping, so that its own Kerberos ticket be used to authenticate.

> Having to trust the OS user and superusers with those credentials isn't any 
> different from using passwords with postgres_fdw.

OS user and superusers, should not have access and allowed to manage the 
credential files.

For example, in a secure environment with separation of duties at the 
organization level (tier1, tier3, superuser, sys admins, etc), the tier1 DB 
users cannot connect onto the DB server (as OS user), but may move data form 
one database to another.
I agree that tier1 users cannot query the catalog and see other user password, 
but a superuser can, which is considered a security breach by auditors.
Storing a password in plain text even for a short period of time is 
unfortunately not authorized.

Thanks!

Jean-Marc Lessard
Administrateur de base de données / Database Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com

[GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

[Patrick Baker]

Hi guys,

I need a function ( PL/PGSQL ) to perform a deletion of some BLOBS...

I have four tables:

*- original_table1_b =* Original table, where the BLOBS are
> *- table1_n_b =* Table where everything related to the BLOBS is stored
> (file_id, account_id, note_id, etc)
> *- table2_y_b =* Table BACKUP - The blobs+data will be copied to here
> before being deleted
> *- table3_n_b =* On the *table1_n_b*, each blob is related to a note_id.
> Each note_id has three different file_id. I want to delete just the
> greatest one. So on this *table3_n_b* table I'm storing the greates
> file_id (by size)



How is the *table3_n_b* table created:

SELECT * INTO table3_n_b FROM(
SELECT account_id, note_id, st_ino, size FROM
(
SELECT DISTINCT ON
(note_id) note_id,
MAX(size),
file_id,
id
FROM
table1_n_b
GROUP BY
note_id, size, file_id, id
ORDER BY
note_id, size desc
) AS r1) AS r2;


The function must perform the following:

1 - Select *note_id + size + file_id + full_path* from *table1_n_b* table
to the new *table2_y_b* one, but only those file_id that are greatest, so
here we use the table created above: *table3_n_b*:

- Something like this?

INSERT INTO table2_y_b (note_id, size, file_id, full_path)
> (
> SELECT
> t1.note_id,
> t1.size,
> t1.file_id,
> t1.full_path
> INTO
> table2_y_b
> FROM
> table1_n_b t1
> JOIN
> table3_n_b t3 ON t3.file_id = t1.file_id
> )


2 - Once the Blob's data is inside the *table2_y_b* table, we can now copy
the blobs into the same table.

- something like this?

INSERT INTO table2_y_b (data)
> (
> SELECT
> o1.data
> FROM
> original_table1_b o1
> JOIN
> table3_n_b t3 ON t3.file_id = o1.file_id
> )


3 - Changing the table2_y_b.migrated column from 0 to 1 (1 means the blob
has been already copied):

> FOR crtRow IN execute
> 'UPDATE table2_y_b SET migrated = 1 WHERE file_id = crtRow.file_id AND
> migrated = 0 ' || $1 ||' offset '||



4 - After we have a backup of the blobs+data, we can now delete the blob
(setting the column as NULL)

> FOR crtRow IN execute

'UPDATE original_table1_b SET data = NULL WHERE file_id = crtRow.file_id '
> || $1 ||' offset '||



*This is what I've done so far:*

CREATE or REPLACE FUNCTION function_1_name(rows integer)

RETURNS INTEGER AS $$


declare

  completed integer;

  crtRow record;


BEGIN

  offset_num = 0;


-- Copiyng the data into the table which will store the data+blobs

FOR crtRow IN execute

'INSERT INTO table2_y_b (note_id, size, file_id, full_path)

(

SELECT

t1.note_id,

t1.size,

t1.file_id,

t1.full_path

INTO

table2_y_b

FROM

table1_n_b t1

JOIN

table3_n_b t3 ON t3.file_id = t1.file_id

) ' || $1 ||' offset '||


-- Copying the BLOBS

FOR crtRow IN execute

'INSERT INTO table2_y_b (data)

(

SELECT

o1.data

FROM

original_table1_b o1

JOIN

table3_n_b t3 ON t3.file_id = o1.file_id

JOIN

table2_y_b t2 ON t2.file_id = o1.file_id

WHERE

t2.migrated = 0

) ' || $1 ||' offset '||


-- Update the migrated column from 0 to 1, for those rows that have been
modified/copied.

FOR crtRow IN execute

'UPDATE table2_y_b SET migrated = 1 WHERE file_id = crtRow.file_id AND
migrated = 0 ' || $1 ||' offset '||


FOR crtRow IN execute

'UPDATE original_table1_b SET data = NULL WHERE file_id = crtRow.file_id '
|| $1 ||' offset '||



RETURN file_id;


END


$$ language 'plpgsql';



Am I doing right?
When I will call the function: *select function_1_name(5000) or **select
function_1_name(15000)* will it respect the limited by the rows?

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

[David G. Johnston]

On Wed, Jun 1, 2016 at 8:10 PM, Patrick Baker 
wrote:

> Hi guys,
>
> I need a function ( PL/PGSQL ) to perform a deletion of some BLOBS...
>
>
​[...]
​

> When I will call the function: *select function_1_name(5000) or **select
> function_1_name(15000)* will it respect the limited by the rows?
>

​I'd suggest you setup a test environment with some unimportant data on a
non-production machine and try it yourself.
​
​​

David J.
​

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

[Patrick Baker]

>
>
> ​I'd suggest you setup a test environment with some unimportant data on a
> non-production machine and try it yourself.
> ​
> ​​
>
> David J.
> ​
>
>
>
Thanks.. but if I'm asking the list that's because I'm already testing it
and it's not working... ;)


Patrick

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

[David G. Johnston]

On Wednesday, June 1, 2016, Patrick Baker  wrote:

>
>
>>
>> ​I'd suggest you setup a test environment with some unimportant data on a
>> non-production machine and try it yourself.
>> ​
>> ​​
>>
>> David J.
>> ​
>>
>>
>>
> Thanks.. but if I'm asking the list that's because I'm already testing it
> and it's not working... ;)
>
>
Are you getting errors?  A quick look seemed like it shouldn't even run do
to syntax problems.

David J.

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

[Adrian Klaver]

On 06/01/2016 05:10 PM, Patrick Baker wrote:

Hi guys,

I need a function ( PL/PGSQL ) to perform a deletion of some BLOBS...

I have four tables:

*- original_table1_b =* Original table, where the BLOBS are
*- table1_n_b =* Table where everything related to the BLOBS is
stored (file_id, account_id, note_id, etc)
*- table2_y_b =* Table BACKUP - The blobs+data will be copied to
here before being deleted
*- table3_n_b =* On the *table1_n_b*, each blob is related to a
note_id. Each note_id has three different file_id. I want to delete
just the greatest one. So on this *table3_n_b* table I'm storing the
greates file_id (by size)



How is the *table3_n_b* table created:

|SELECT*INTOtable3_n_b FROM(SELECTaccount_id,note_id,st_ino,size
FROM(SELECTDISTINCTON(note_id)note_id,MAX(size),file_id,id
FROMtable1_n_b GROUPBYnote_id,size,file_id,id ORDERBYnote_id,size
desc)ASr1 )ASr2;|


The function must perform the following:

1 - Select /_note_id + size + file_id + full_path_/ from *table1_n_b*
table to the new *table2_y_b* one, but only those file_id that are
greatest, so here we use the table created above: *table3_n_b*:

- Something like this?

INSERT INTO table2_y_b (note_id, size, file_id, full_path)
(
SELECT
t1.note_id,
t1.size,
t1.file_id,
t1.full_path
INTO
table2_y_b
FROM
table1_n_b t1
JOIN
table3_n_b t3 ON t3.file_id = t1.file_id
)


2 - Once the Blob's data is inside the *table2_y_b* table, we can now
copy the blobs into the same table.

- something like this?

INSERT INTO table2_y_b (data)
(
SELECT
o1.data
FROM
original_table1_b o1
JOIN
table3_n_b t3 ON t3.file_id = o1.file_id
)


3 - Changing the table2_y_b.migrated column from 0 to 1 (1 means the
blob has been already copied):

FOR crtRow IN execute
'UPDATE table2_y_b SET migrated = 1 WHERE file_id = crtRow.file_id
AND migrated = 0 ' || $1 ||' offset '||



4 - After we have a backup of the blobs+data, we can now delete the blob
(setting the column as NULL)

FOR crtRow IN execute

'UPDATE original_table1_b SET data = NULL WHERE file_id =
crtRow.file_id ' || $1 ||' offset '||



*This is what I've done so far:*

CREATE or REPLACE FUNCTION function_1_name(rows integer)

RETURNS INTEGER AS $$


declare

  completed integer;

  crtRow record;


BEGIN

  offset_num = 0;


-- Copiyng the data into the table which will store the data+blobs

FOR crtRow IN execute

'INSERT INTO table2_y_b (note_id, size, file_id, full_path)

(

SELECT

t1.note_id,

t1.size,

t1.file_id,

t1.full_path

INTO

table2_y_b

FROM

table1_n_b t1

JOIN

table3_n_b t3 ON t3.file_id = t1.file_id

) ' || $1 ||' offset '||


-- Copying the BLOBS

FOR crtRow IN execute

'INSERT INTO table2_y_b (data)

(

SELECT

o1.data

FROM

original_table1_b o1

JOIN

table3_n_b t3 ON t3.file_id = o1.file_id

JOIN

table2_y_b t2 ON t2.file_id = o1.file_id

WHERE

t2.migrated = 0

) ' || $1 ||' offset '||


-- Update the migrated column from 0 to 1, for those rows that
have been modified/copied.

FOR crtRow IN execute

'UPDATE table2_y_b SET migrated = 1 WHERE file_id =
crtRow.file_id AND migrated = 0 ' || $1 ||' offset '||


FOR crtRow IN execute

'UPDATE original_table1_b SET data = NULL WHERE file_id =
crtRow.file_id ' || $1 ||' offset '||



RETURN file_id;


END


$$ language 'plpgsql';



Am I doing right?
When I will call the function: *select function_1_name(5000) or **select
function_1_name(15000)* will it respect the limited by the rows?



I maybe be missing it, but I see no LIMIT in the function.

I do see OFFSET and it looks backwards to me?:

|| $1 ||' offset '||

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

LIMIT Clause

The LIMIT clause consists of two independent sub-clauses:

LIMIT { count | ALL }
OFFSET start

Also I not sure what offset_num is supposed to do, it is declared but 
n

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

[Patrick Baker]

>
>
>>
> I maybe be missing it, but I see no LIMIT in the function.
>
> I do see OFFSET and it looks backwards to me?:
>
> || $1 ||' offset '||
>
> https://www.postgresql.org/docs/9.5/static/sql-select.html
>
> LIMIT Clause
>
> The LIMIT clause consists of two independent sub-clauses:
>
> LIMIT { count | ALL }
> OFFSET start
>
> Also I not sure what offset_num is supposed to do, it is declared but not
> used?


Yep.. it's declared but it's not used..

Hmm.. interesting that about the LIMIT clause
However.. I had to changed the function...

Here is the new one:

CREATE or REPLACE FUNCTION function_data_1(rows integer)

RETURNS INTEGER AS $$


declare

  completed integer;

  offset_num integer;

  crtRow record;


BEGIN

  offset_num = 0;


INSERT INTO table2_y_b (note_id, size, file_id, full_path)

(

SELECT

t1.note_id,

t1.size,

t1.file_id,

t1.full_path

FROM

table1_n_b t1

JOIN

table3_n_b t3 ON t3.file_id = t1.file_id

);


UPDATE table2_y_b t2 SET segment_data =

(

SELECT

o1.data

FROM

original_table1_b o1

JOIN

table3_n_b t3 ON t3.file_id = o1.file_id

WHERE

t2.migrated = 0

AND

t2.file_id = o1.file_id

);


UPDATE table2_y_b SET migrated = 1 WHERE file_id = crtRow.file_id AND
migrated = 0;


UPDATE original_table1_b SET data = NULL WHERE file_id = crtRow.file_id;


END


$$ language 'plpgsql';


It's all working, except the LIMIT...  if possible can you please give me
an example of that LIMIT in some of those queries?

Thanks

[GENERAL] RowDescription via the SQL?

[Dmitry Igrishin]

Hi,

It's possible to query pg_prepared_statements view to obtain the
information about
parameters used in the statement that was prepared. But I don't found
how to get the
information about the rows that will be returned when the statement is
eventually executed.
(It's possible to get this information via the frontend/backend
protocol as a response
on the Describe.) But out of interest, is it possible to get such
information via the SQL?

-- 
// Dmitry.


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