Re: [GENERAL] Secret Santa List
Why not generate the required results in a SELECT then update from that. row_number() could allow you to generate a random number to each giver, then we can generate another random number and join to each random number. That'll give you a giver and recipient combination. e.g: select giver,recipient from (select row_number() over (order by random()) rn, giver from secretsanta) g inner join (select row_number() over (order by random()) rn, giver recipient from secretsanta) r on g.rn = r.rn You can then wrap that up in a CTE, something along the lines of: with cte (giver, recipient) as ( select giver,recipient from (select row_number() over (order by random()) rn, giver from secretsanta) g inner join (select row_number() over (order by random()) rn, giver recipient from secretsanta) r on g.rn = r.rn ) update secretsanta set recipient = cte.recipient from cte WHERE cte.giver = secretsanta.giver; Hey, I think that works! Thanks! -- 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] Secret Santa List
Of course: you can't UPDATE a field with a query returning more than one result, as you can check easily trying: I understand that, and my query does not return more than one result. The problem is that it returns THE SAME result each time, most likely because the subquery is evaluated exactly once and then the main query uses that single result over and over. update secretsanta set recipient = ( select giver from secretsanta s2 where not exists (select * from secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 ); My hope is to somehow persuade PostgreSQL to re-evaluate the subquery each time, and see that the set of available recipients has changed. If "Steve" was picked for the first row, "Steve" shouldn't be available for any subsequent row. If "Fred" was picked for the second row, neither "Steve" nor "Fred" should be available for any subsequent row. You could get a list of givers in no particular order (e. g. "select giver from secretsanta order by md5(concat(giver,current_time))") then setting each employee as next's employee giver. As in, write a loop in some programming language to update the table one row at a time, or did you envision a way to do this with an SQL statement? I can certainly write a loop, if that's the only solution. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Secret Santa List
I have a company with four employees who participate in a Secret Santa
program, where each buys a gift for an employee chosen at random. (For
now, I do not mind if an employee ends up buying a gift for himself.)
How can I make this work with an SQL statement?
Here is my Secret Santa table:
--
create table secretsanta
(giver text,
recipient text,
primary key (giver));
insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
('Earl');
--
Here is the SQL statement I am using to populate the "recipient" column:
--
update secretsanta set recipient =
( select giver from secretsanta s2 where not exists (select * from
secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
--
The problem: every time I run this, a single name is chosen at random
and used to populate all the rows. So all four rows will get a
recipient of "Steve" or "Earl" or whatever single name is chosen at random.
I suppose the problem is that the "exists" subquery does not re-evaluate
for each record. How do I prevent this from happening? Can I use a
"lateral" join of some kind, or somehow tell PostgreSQL to not be so
optimized?
--
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] requests / suggestions to help with backups
> >Certainly, I've > >tried "grant select on database mydatabase to user myuser"; it doesn't > >work, because "select" is not a database-level privilege. > Sorry, you're right on that one. I misread it. However, it shouldn't > be too hard to write a script, either in a procedural language or higher > level, to pull the existing table names from pg_class and invokes the > GRANT command for you "trusted" user on each. That could be done, but my big worry is all the non-table components of a database such as views and functions -- I'd hate to accidentally be creating incomplete dumps simply because I forgot to programmatically assign permissions on my operator classes (or whatever). So I'd still like to see a "read" or "readonly" permission at the database level, but until then, it seems the best bet is to use an overprivileged trusted account for my backups. The security risks can be managed, and they are worth it to make sure I've got a complete and cohesive dump. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] requests / suggestions to help with backups
> Lou Duchez wrote: > >Like everyone else, I use pg_dump for backup purposes; I have a cron job > >that runs a pg_dump whose output is then FTP'd elsewhere. Two things > >that would make my life easier: > > > >1) "grant select on database ..." or, hypothetically, "grant select on > >cluster". The goal would be to create a read-only PostgreSQL user, one > >who can read the contents of an entire database (or even the entire > >cluster) but make no changes. Currently, to do my cron job, I have to > >specify a "trusted" user, otherwise PostgreSQL will ask for a password; > >it sure would be nice if I could neuter my "trusted" user so he cannot > >do any damage. (Yes, I could set read-only privileges on a table-by-table > >basis. Obviously, that's a pain.) > > > >2) "pg_dumpall -E". If I could specify a single encoding for all my > >database dumps, I could use pg_dumpall. But I cannot. (My databases > >themselves are encoded as UTF-8, but the data in them is all LATIN1, and > >I'd like to dump it all as LATIN1.) There are quite possibly good > >reasons for not offering the "-E" option on pg_dumpall; in the wrong > >hands it could be nightmarish. But sensibly employed, it could be very > >useful. > > > >And, combining my two requests, a "grant select on cluster ..." would > >allow me to do something like: > > > >pg_dumpall -U neutereduser -E LATIN1 -f onehugefile.bak > > > >I could really go for that. Especially when there's a major upgrade to > >PostgreSQL. > I guess you missed this: > http://www.postgresql.org/docs/8.2/interactive/sql-grant.html > You want the third one down. So are you recommending I use "grant create", "grant connect", "grant temporary", "grant temp", or "grant all"? Those seem to be the only permissions that can be applied on a database level. Certainly, I've tried "grant select on database mydatabase to user myuser"; it doesn't work, because "select" is not a database-level privilege. So unless you know a database-level permission that means "read-only", I think I'm still stuck. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] requests / suggestions to help with backups
Like everyone else, I use pg_dump for backup purposes; I have a cron job that runs a pg_dump whose output is then FTP'd elsewhere. Two things that would make my life easier: 1) "grant select on database ..." or, hypothetically, "grant select on cluster". The goal would be to create a read-only PostgreSQL user, one who can read the contents of an entire database (or even the entire cluster) but make no changes. Currently, to do my cron job, I have to specify a "trusted" user, otherwise PostgreSQL will ask for a password; it sure would be nice if I could neuter my "trusted" user so he cannot do any damage. (Yes, I could set read-only privileges on a table-by-table basis. Obviously, that's a pain.) 2) "pg_dumpall -E". If I could specify a single encoding for all my database dumps, I could use pg_dumpall. But I cannot. (My databases themselves are encoded as UTF-8, but the data in them is all LATIN1, and I'd like to dump it all as LATIN1.) There are quite possibly good reasons for not offering the "-E" option on pg_dumpall; in the wrong hands it could be nightmarish. But sensibly employed, it could be very useful. And, combining my two requests, a "grant select on cluster ..." would allow me to do something like: pg_dumpall -U neutereduser -E LATIN1 -f onehugefile.bak I could really go for that. Especially when there's a major upgrade to PostgreSQL. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
