[GENERAL] Duplicated tables of certain columns

[Yan Cheng CHEOK]

I try to duplicate a tables of certain columns by using

CREATE TABLE backup_table AS SELECT * FROM unit_11 WHERE status = 1;

I realize the above command will duplicate content of table unit_11 to 
backup_table. However, the index is not being carried over. Hence, I change my 
command to

create table backup_table ( like unit_11 INCLUDING DEFAULTS INCLUDING 
CONSTRAINTS INCLUDING INDEXES );
INSERT INTO backup_table SELECT * FROM unit_11 WHERE status = 1;

It works fine with the following output


Table unit_11
=
unit_id [PK]fk_lot_id   status  value
1   11  1   100
2   11  1   101
3   11  0   102


Table backup_table
==
unit_id [PK]fk_lot_id   status  value
1   11  1   100
2   11  1   101

However, this is not what I want. I wish to have all columns being duplicated 
over except for column "fk_lot_id", where I would like to define my own 
"fk_lot_id". My final wished table is as follow.


Table backup_table
==
unit_id [PK]fk_lot_id   status  value
1   99  1   100
2   99  1   101

May I know how I can achieve these by using combination of SQL command?

Thanks!

Thanks and Regards
Yan Cheng CHEOK


  

-- 
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] Duplicated tables of certain columns

[Vibhor Kumar]

On Feb 21, 2011, at 1:39 PM, Yan Cheng CHEOK wrote:

> INSERT INTO backup_table SELECT * FROM unit_11 WHERE status = 1;



You can try something like,
INSERT INTO backup_table select unit_id, 99, status, value from unit_11 where 
status=1;


Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Cell: +91-932-568-2279
vibhor.ku...@enterprisedb.com
Blog:http://vibhork.blogspot.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] Schema Archive cant find table

[Vibhor Kumar]

On Feb 18, 2011, at 9:10 PM, trex005 wrote:

> I am trying to archive a schema, however there is a certain table that
> gets skipped with pd_dump
> 
> The table's name is 'servers'.  I checked to see if it is a reserved
> word, and it does not appear to be...
> 
> Thinking I was missing something I tried using -t servers, and I get :
> pg_dump: No matching tables were found
> 
It should not. Try schemaname.tablename with switch -t.



Thanks & Regards,
Vibhor Kumar

-- 
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] Duplicated tables of certain columns

[Yan Cheng CHEOK]

Sorry. The table visualize display doesn't look right in the message, as I am 
using tab instead of space. I fix them.

Table unit_11
=
unit_id [PK]fk_lot_id   status  value
1   11  1   100
2   11  1   101
3   11  0   102


Table backup_table (Current)
==
unit_id [PK]fk_lot_id   status  value
1   11  1   100
2   11  1   101


Table backup_table (What I wish to have)
==
unit_id [PK]fk_lot_id   status  value
1   99  1   100
2   99  1   101


Thanks and Regards
Yan Cheng CHEOK


--- On Mon, 2/21/11, Yan Cheng CHEOK  wrote:

> From: Yan Cheng CHEOK 
> Subject: Duplicated tables of certain columns
> To: pgsql-general@postgresql.org
> Date: Monday, February 21, 2011, 4:09 PM
> I try to duplicate a tables of
> certain columns by using
> 
> CREATE TABLE backup_table AS SELECT * FROM unit_11 WHERE
> status = 1;
> 
> I realize the above command will duplicate content of table
> unit_11 to backup_table. However, the index is not being
> carried over. Hence, I change my command to
> 
> create table backup_table ( like unit_11 INCLUDING DEFAULTS
> INCLUDING CONSTRAINTS INCLUDING INDEXES );
> INSERT INTO backup_table SELECT * FROM unit_11 WHERE status
> = 1;
> 
> It works fine with the following output
> 
> 
> Table unit_11
> =
> unit_id [PK]    fk_lot_id   
> status        value
> 1        11   
>     1       
> 100
> 2        11   
>     1       
> 101
> 3        11   
>     0       
> 102
> 
> 
> Table backup_table
> ==
> unit_id [PK]    fk_lot_id   
> status        value
> 1        11   
>     1       
> 100
> 2        11   
>     1       
> 101
> 
> However, this is not what I want. I wish to have all
> columns being duplicated over except for column "fk_lot_id",
> where I would like to define my own "fk_lot_id". My final
> wished table is as follow.
> 
> 
> Table backup_table
> ==
> unit_id [PK]    fk_lot_id   
> status        value
> 1        99   
>     1       
> 100
> 2        99   
>     1       
> 101
> 
> May I know how I can achieve these by using combination of
> SQL command?
> 
> Thanks!
> 
> Thanks and Regards
> Yan Cheng CHEOK
> 
> 
>       
> 




-- 
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] Worst case scenario of a compromised non super-user PostgreSQL user account

[Craig Ringer]

On 21/02/2011 3:44 PM, Allan Kamau wrote:


Are there other problems we may expect. Can they run any OS programs
or install any such tools, induce buffer overflows and so on.?


So long as your webapp user and database owner is a regular user 
(non-superuser) without CREATE ROLE or CREATE DATABASE rights, they 
should be pretty well sandboxed, though as you point out they can 
attempt to affect the rest of the system by running expensive queries.


If your webapp user is a superuser and the webapp gets cracked, you're 
screwed. Don't do it. Ever.


To be even safer, you should consider not even making your webapp user 
the database owner. Have a different user create the database, own it, 
and run the DDL to create and maintain its tables. Explicitly GRANT the 
webapp user the rights it requires on the tables, views and functions it 
uses, and no more. This may not be practical if your webapp likes to run 
its own generated DDL during upgrades (like Rails, Drupal, etc) or lacks 
any sort of documentation on what access rights it needs.


--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.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] ?????? [GENERAL] How to make the user-defined function or type beingaccessed in the global database server?

[Craig Ringer]

On 20/02/2011 2:13 PM,  wrote:

Thanks for your replying. But actually there are some difference
database now, and I really want to define the user-defined function or
type one time so that they can be accessed as system-defined function or
type, how can I do?


It sounds like you want something like Oracle's packages. If so, you're 
out of luck: postgresql doesn't support anything like that at this point.


Bundle the functions, type definitions, etc up in a .sql file and run 
that on each database. It's cleanest if you do this by creating a new 
schema called, say, 'shared', then SET search_path=shared before 
creating the functions and types. That way they all go in one place 
that's separated from the per-database content. To access them, SET 
search_path=shared,public . You can "ALTER USER SET 
search_path=shared,public" or "ALTER DATABASE SET 
search_path=shared,public" to make it default.


See the manual for more information about schema, and the commands/options:

  SET
  search_path
  CREATE SCHEMA
  CREATE FUNCTION

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.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] ?????? [GENERAL] How to make the user-defined function or type beingaccessed in the global database server?

[John R Pierce]

On 02/19/11 10:13 PM,  wrote:
Thanks for your replying. But actually there are some difference 
database now, and I really want to define the user-defined function or 
type one time so that they can be accessed as system-defined function 
or type, how can I do? Thanks!




an alternative might be to define your functions in the template 
database used to create your other databases, then it will be copied 
into each of them at CREATE DATABASE time.


this of course is of no use if these databases already exist.




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

[GENERAL] Drop CHECK Constraint

[Yan Cheng CHEOK]

I was wondering, is there any good way to drop a constraint? Currently, I am 
making assumption on the check constraint name. 

Although it works at current moment, I do not feel comfortable with it in long 
run.

For example :


ALTER TABLE backup_table ADD CHECK (fk_lot_id = 99);

If I want to drop the above CHECK constraint, I will do

ALTER TABLE backup_table DROP CONSTRAINT backup_table_fk_lot_id_check; (I 
assume the constraint name will be backup_table_fk_lot_id_check)

Is there any more robust way?

Thanks and Regards
Yan Cheng CHEOK


  

-- 
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] Drop CHECK Constraint

[Christian Ullrich]

* Yan Cheng CHEOK wrote:


I was wondering, is there any good way to drop a constraint? Currently, I am 
making assumption on the check constraint name.



ALTER TABLE backup_table ADD CHECK (fk_lot_id = 99);

If I want to drop the above CHECK constraint, I will do

ALTER TABLE backup_table DROP CONSTRAINT backup_table_fk_lot_id_check; (I 
assume the constraint name will be backup_table_fk_lot_id_check)

Is there any more robust way?


Name your constraints:

ALTER TABLE backup_table ADD CONSTRAINT lot_id_ck CHECK (fk_lot_id = 99)


Alternatively, you can get a list of constraints for your table from the 
catalog:


SELECT conname
  FROM pg_constraint
 WHERE conrelid = 'backup_table'::regclass

You may have to add more conditions to the query.

--
Christian


--
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] multiple tables as a foreign key

[Sim Zacks]

On 02/21/2011 12:40 AM, matty jones wrote:

I am not sure if this is possible but is there a way that I can have 
multiple columns from different tables be a foreign key to a single 
column in another table, or do I need to write a check function and if 
so how could I set up a relation?


CREATE TABLE seriestitle (
seriestitletext
);
CREATE TABLE compilationtitle (
compilationtitletextPRIMARY KEY,
pubddatetextNOT NULL,
isbntextNOT NULL,
styletextREFERENCES style,
storylinetextREFERENCES storyline(storyline) DEFAULT '_default_',
seriestitletextREFERENCES seriestitle DEFAULT '_default_',
pricetextNOT NULL,
);

CREATE TABLE storytitle (
storytitletextPRIMARY KEY,
notestextDEFAULT '_default_',
);

CREATE TABLE issuetitle (
issuetitletextPRIMARY KEY,
pubdatetextNOT NULL,
pricetextNOT NULL,
bookcoverOIDREFERENCES bookcover(bookcover),
compilationtitletextREFERENCES compilation(compilation) DEFAULT 
'_default_',

seriestitletextREFERENCES seriestitle DEFAULT '_default_',
);
CREATE TABLE noveltitle (
noveltitletextNOT NULL,
isbntextNOT NULL,
pubdatetextNOT NULL,
pricetextNOT NULL,
bookcoverOIDREFERENCES bookcover(bookcover),
seriestitletextREFERENCES seriestitle DEFAULT '_default_',
);

The seriestitle table will contain a list of all the series names that 
I am collecting but I want to be able to relate them to the 
issuetitle, compilationtitle, and noveltitle tables.  My thoughts were 
using a foreign key to do this.  Create an attribute in seriestitle 
called booktitle and have that be referenced from the other three but 
that doesn't seem possible or at least I couldn't find out how in the 
documentation.  If I were to create three separate attributes for each 
of the separate titles in the seriestitle table then reference those 
attributes from their respective tables that would produce errors I 
believe, because a foreign key can't be null and not every attribute 
will have a value in every tuple.  In reading about normalization a 
single attribute cannot contain multiple values.  For example:


INSERT INTO issuetitle (seriestitle)
VALUES ('batman, catwoman')
;

Now the seriestitle table would contain 'batman, catwoman' for a value 
but that would break normalization rules


The only thing left that I can think of is to create some sort of a 
function that checks to see whether the value being entered into 
noveltitle, issuetitle, or compilationtitle is contained within 
seriestitle but then how would I go about relating a row from one of 
those three tables to a row in seriestable from a single attribute 
without having to do a lot of manual work?


Thanks.


First of all you can have a null foreign key. However, in this case that 
is probably not what you actually want to do.

You probably want the series table with a serial primary key.
You then want the compilation table referencing the series table id.
The stories table will either reference the compilation table or the 
series table and so on.

You can have multiple table referencing the same key in another table.

Sim

Re: [GENERAL] password

[Roedy Green]

On Sun, 20 Feb 2011 21:44:22 -0800, pie...@hogranch.com (John R
Pierce) wrote, quoted or indirectly quoted someone who said :

>when you initially connect to postgres wtih psql or pgadmin-III, specify 
>the user as `postgres` and then once connected,

It would not let me in even once.
-- 
Roedy Green Canadian Mind Products
http://mindprod.com
Refactor early. If you procrastinate, you will have
even more code to adjust based on the faulty design.
.


-- 
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] password

[Roedy Green]

On Mon, 21 Feb 2011 10:45:14 +0530, sachin.srivast...@enterprisedb.com
(Sachin Srivastava) wrote, quoted or indirectly quoted someone who
said :

>Hello,
>
>Installation of postgresql requires you to enter a password for the user 
>'postgres'.
>
> i) If the user 'postgres' is not there, it will create it and set the 
> password to whatever you have provided,
> ii) If the user 'postgres' is already existing, then you have to give its 
> password to move further in the installation.
>
>You can use any account other that 'postgres' by giving CLI option 
>'---serviceaccount '. See --help for more details.
>
>In case you dont remember the password you set for user 'postgres' then you 
>can change the same via "Right Click My Computer"-->"Manage"-> Users.. 
>
>You said it rejects the password.  When ? 
>
>And windows user are most welcome here.
>
>
>On Feb 21, 2011, at 7:40 AM, Roedy Green wrote:
>
>> I gave Postgre a password during install.  However, it always rejects
>> it. I tried uninstalling, deleting all files, and reinstalling. Same
>> thing.  It complains about user "roedy" (my windows id). It seems to
>> me the default user is supposed to be postgres not Roedy.  Perhaps
>> that is the source of the problem. I can't find anything relevant in
>> the docs.
>> 
>> The docs talk about installing on Unix by compiling C source.  I have
>> Windows 7 64 bit. I get the feeling Windows users are unwelcome.
>> -- 
>> Roedy Green Canadian Mind Products
>> http://mindprod.com
>> Refactor early. If you procrastinate, you will have
>> even more code to adjust based on the faulty design.
>> .
>> 
>> 
>> -- 
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general

This did not help.  However, I have got it going.  Part of my problem
came from expecting it to work identically to MySQL.

I have posted my notes at
http://mindprod.com/jgloss/postgresql.html#GOTCHAS

The key was setting up ENV parms to get it to default to postgres as
the user id.  It was defaulting to roedy, my windows id.  I later
discovered I could force it to use postgres with the -U option. At
first this did not appear to work.

-- 
Roedy Green Canadian Mind Products
http://mindprod.com
Refactor early. If you procrastinate, you will have
even more code to adjust based on the faulty design.
.


-- 
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] Worst case scenario of a compromised non super-user PostgreSQL user account

[Andrew Sullivan]

On Mon, Feb 21, 2011 at 10:44:05AM +0300, Allan Kamau wrote:

> A web application requires a dedicated PostgreSQL database in which to
> create tables and other database objects and manipulate data within
> this single database.

Why does the web application need to create tables?

I usually prefer to have two accounts: one owns the objects, and
another that has INSERT/DELETE/UPDATE and so on permissions.

If the application is creating tables, you might want to ask yourself why.

Other than that, what others said.

A


-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
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] disable triggers using psql

[Geoffrey Myers]

Greg Sabino Mullane wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


I'm not sure how to address this.  I'm not exactly sure where to place 
session_replication_role.  It's very close to the top of the file:


Is this a pg_dumpall? A \connect later on will reset the 
session_replication_role. If so, add the SET right after 
the \connect. Alternatively, you could create a special user 
to invoke psql as, which has:


It is a pg_dump.  There is no \connect in the code.



ALTER USER dangerous_bob SET session_replication_role = replica;

Be *very* careful with that account though, as using it for 
anything other than this special case could be very bad.


- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102181408
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1ew9MACgkQvJuQZxSWSshETwCg2oEEicHhokORuQRl3sxkLkpj
ghIAnRe02LCuyyRlyzvKZ67QCYUyfPzC
=H9Wb
-END PGP SIGNATURE-






--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

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

[GENERAL] Continuous recovery stopping

[Norberto Delle]

Hi all

I have a hot standby replication setup.
But for no apparent reason, it stops the recovery. Here is a part of the 
log:


2011-02-21 11:07:03 BRT LOG: restored log file 
"00010009001B" from archive
2011-02-21 11:07:33 BRT LOG: restored log file 
"00010009001C" from archive
2011-02-21 11:08:03 BRT LOG: restored log file 
"00010009001D" from archive
2011-02-21 11:11:34 BRT LOG: could not open file 
"pg_xlog/00010009001E" (log file 9, segment 30): No such 
file or directory

2011-02-21 11:11:34 BRT LOG: redo done at 9/1DA2E410
2011-02-21 11:11:34 BRT LOG: last completed transaction was at log time 
2011-02-21 09:12:00.61-03
2011-02-21 11:12:04 BRT LOG: restored log file 
"00010009001D" from archive

2011-02-21 11:15:04 BRT LOG: selected new timeline ID: 2
2011-02-21 11:18:04 BRT LOG: archive recovery complete
Formato de parĭetro incorreto - "chive".

2011-02-21 11:18:04 BRT WARNING: recovery_end_command "del 
D:/Archive/026/failover.trigger": return code 1

2011-02-21 11:18:05 BRT LOG: database system is ready to accept connections

And here is my recovery.conf file:

restore_command = '"C:/program files 
(x86)/postgresql/9.0/bin/pg_standby.exe" -s 30 -t 
D:/Archive/026/failover.trigger D:/archive/026 %f %p %r 
2>>D:/archive/026/pg_standby.log'

trigger_file = 'D:/Archive/026/failover.trigger'
recovery_end_command = 'del D:/Archive/026/failover.trigger'

What can be making it stop the recovery. Any ideas?

Thanks

Norberto Dellê

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

[GENERAL] schema Cleanning

[salah jubeh]

Hello,

I am trying to drop deprecated tables, views, procedures from an existing 
database, I am checking the pg_stat_user_tables view to check if the tables are 
accessed recently also to check live tuples. For deprecated views and 
procedures, I am wondering How can I do that, for example suppose the table is 
used and there is a view defined using this table but no one is using it. Is 
there is away to check the views statistics. 


Regards  


  

Re: [GENERAL] disable triggers using psql

[Geoffrey Myers]

Greg Sabino Mullane wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


I'm not sure how to address this.  I'm not exactly sure where to place 
session_replication_role.  It's very close to the top of the file:


Is this a pg_dumpall? A \connect later on will reset the 
session_replication_role. If so, add the SET right after 
the \connect. Alternatively, you could create a special user 
to invoke psql as, which has:


ALTER USER dangerous_bob SET session_replication_role = replica;

Be *very* careful with that account though, as using it for 
anything other than this special case could be very bad.


The saga continues.  I've reverted to a multi-step process to try and 
figure this out.  I create the initial database, then load it from the 
command line psql as follows:


pro-# \set session_replication_role replica;
pro-# \o db.out
pro-# \i dump.txt

I still get:

psql:dump.txt:2077301: ERROR:  insert or update on table "cust" violates 
foreign key constraint "$1"

DETAIL:  Key (country,state)=(US,GA) is not present in table "state".


So there's something in the dump that's changing the 
session_replication_role?




- -- 
Greg Sabino Mullane g...@turnstep.com

End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201102181408
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk1ew9MACgkQvJuQZxSWSshETwCg2oEEicHhokORuQRl3sxkLkpj
ghIAnRe02LCuyyRlyzvKZ67QCYUyfPzC
=H9Wb
-END PGP SIGNATURE-






--
Until later, Geoffrey

"I predict future happiness for America if they can prevent
the government from wasting the labors of the people under
the pretense of taking care of them."
- Thomas Jefferson

--
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] disable triggers using psql

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


> The saga continues.  I've reverted to a multi-step process to try and 
> figure this out.  I create the initial database, then load it from the 
> command line psql as follows:
> 
> pro-# \set session_replication_role replica;
> pro-# \o db.out
> pro-# \i dump.txt

This is a database set, not a psql on, so you do not want the 
backslash before the "set". 

SET session_replication_role = replica;

I'd recommend adding a:

SHOW session_replication_role;

to the dump.txt as a sanity check.

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

iEYEAREDAAYFAk1iy74ACgkQvJuQZxSWSsgWQACgrxKDvN/yCZD5GZJvlqFMyyIC
9mwAnjOMJ9QDRa3IoiBCvaS9mT5sMR6f
=JYCs
-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] Deadlock on the same select for update

[Roman]

Hi,
I have problem with deadlocks and don't know why it happens. Below is
the log (postgres 9.0, debian):

[11882]DETAIL:  Process 11882 waits for ShareLock on transaction
44324308; blocked by process 11884.
Process 11884 waits for ShareLock on transaction 44324307;
blocked by process 11882.

Process 11882: SELECT * FROM teddy WHERE id IN
(112747007,112747008,112747011,112747013,112747015,112747016,112747020,112747021,112747022,112747024,112747025,112747028,112747030,112747032,112747034,112747035,112747038,112747043,112747044,112747045,112747050,112747052,112747053)
FOR UPDATE

Process 11884: SELECT * FROM teddy WHERE id IN
(112747007,112747008,112747011,112747013,112747015,112747016,112747020,112747021,112747022,112747024,112747025,112747028,112747030,112747032,112747034,112747035,112747038,112747043,112747044,112747045,112747050,112747052,112747053)
FOR UPDATE


Regards,
Roman

-- 
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] Deadlock on the same select for update

[Bill Moran]

In response to Roman :

> Hi,
> I have problem with deadlocks and don't know why it happens. Below is
> the log (postgres 9.0, debian):
> 
> [11882]DETAIL:  Process 11882 waits for ShareLock on transaction
> 44324308; blocked by process 11884.
> Process 11884 waits for ShareLock on transaction 44324307;
> blocked by process 11882.
> 
> Process 11882: SELECT * FROM teddy WHERE id IN
> (112747007,112747008,112747011,112747013,112747015,112747016,112747020,112747021,112747022,112747024,112747025,112747028,112747030,112747032,112747034,112747035,112747038,112747043,112747044,112747045,112747050,112747052,112747053)
> FOR UPDATE
> 
> Process 11884: SELECT * FROM teddy WHERE id IN
> (112747007,112747008,112747011,112747013,112747015,112747016,112747020,112747021,112747022,112747024,112747025,112747028,112747030,112747032,112747034,112747035,112747038,112747043,112747044,112747045,112747050,112747052,112747053)
> FOR UPDATE

My experience is that you have no guarantee what order SELECT FOR UPDATE
will lock those rows in, thus the chance for deadlock is there.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

[GENERAL] why is there no TRIGGER ON SELECT ?

[Melvin Davidson]

Other than "It's currently not available", can anyone provide a logical 
explanation of why triggers cannot be implemented for SELECT statements, or 
rules for SELECT must be DO INSTEAD SELECT?

PostgreSQL was derived from Ingres, and Ingres had a nice auditing feature that 
also handled SELECT.  It would be simple enough to write a RULE or TRIGGER on a 
SELECT to just log access, but for some unexplainable reason (at least to my 
knowledge) this has been greatly restricted in PostgreSQL. I am sure many DBA's 
and developers would greatly appreciate the addition of a TRIGGER or RULE on 
SELECT, and it should be simple enough to change the code, so I am curious as 
to why this has never been done.

Thanks in advance.



Melvin Davidson 
 



  

Re: [GENERAL] Schema version control

[ChronicDB Community Team]

> What about upgrades that can't be derived directly from an inspection
> of the schema?  Some examples:
> 
> - Adding a NOT NULL constraint (without adding a DEFAULT).  You often
> want to precede this with filling in any existing NULL values, so the
> new constraint doesn't fail.

This is an important point. The upgrade to the new schema should be
defined with a matching transformer that will initialize such a value.

> - Updating triggers, functions and their effects.  For example, when I
> have an FTS index with a trigger to update an index column, and I
> change the underlying trigger, I often do something like "UPDATE table
> SET column = column", to cause all of the update triggers to fire and
> recalculate the index columns.

This is the kind of work one should not need to write for every upgrade.
The upgrade should be specified as succinctly as possible, and
everything else around it should be automatic.

> - Creating a new column based on an old one, and removing the old one;
> eg. add a column "n", run "UPDATE ... SET n = i*j * 2", and then drop
> the old columns "i" and "j".

Again, a transformer for computing such new columns is clearly needed.
With ChronicDB not only can such a transformer be provided, but also the
old version of the application client (like a web-app) can continue to
use the old schema while maintaining backwards compatible: its query
requests are also transformed.

> - For efficiency, dropping a specific index while making a large
> update, and then recreating the index.
> 
> In my experience, while generating schema updates automatically is
> handy, it tends to make nontrivial database updates more complicated.
> These sorts of things happen often and are an integral part of a
> database update, so I'm just curious how/if you deal with them.

Glenn, I think you have hit the nail on the head here. A more systematic
model for dealing with nontrivial database updates is needed. In the
case of making a large update that recreates an index, one approach
might be to instantiate a new schema that has the new index on it, and
ensure service is switched automatically and with data consistency.

> I've used Ruby's migrations, and for my Django databases I use my own
> migration system which is based in principle off of it: create scripts
> to migrate the database from version X to X+1 and X-1, and upgrade or
> downgrade by running the appropriate scripts in sequence.
> 
> It's not ideal, since it can't generate a database at a specific
> version directly; it always has to run through the entire sequence of
> migrations to the version you want, and the migrations accumulate.
> However, it can handle whatever arbitrary steps are needed to update a
> database, and I don't need to test updates from every version to every
> other version.

This is something we are planning on adding to ChronicDB. Given a list
of database upgrade definitions, the "end result" should be computed so
that one could upgrade from any arbitrary version to any other arbitrary
version.


-- 
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] Schema version control

[ChronicDB Community Team]

On Fri, 2011-02-11 at 08:35 -0500, Daniel Popowich wrote:

> think no software process can make anyone happy.  It's a human
>  process: declare someone the owner of the database schema, let them
>  own the long term development of the schema, and if anyone needs a
>  change, they have to communicate and vet those changes with the db
>  designer.

This cannot be understated. It is very much a human process, and the
database schema owner should be governing the data model. 


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

[GENERAL] string_agg hanging?

[Joel Reed]

Hoping someone will be kind enough to share how to write a query that 
uses 9.0's string_agg with a subselect, something like...


select 
m.id,m.subject,m.from_address,m.date_sent,m.size,string_agg((select 
address as to_address from recipient r inner join message_recipient mr 
on r.id=mr.recipient_id and mr.message_id=m.id and 
mr.recipient_type='To'), ', ') from message m, recipient r group by 
m.id, m.subject, m.from_address, m.date_sent, m.size limit 20;


Unforunately, that just seems to hang. So I'm using...

select 
m.id,m.subject,m.from_address,m.date_sent,m.size,array_to_string(ARRAY(select 
r.address from recipient r inner join message_recipient mr on 
r.id=mr.recipient_id and message_id=m.id and mr.recipient_type='To'), 
',') as to_addresses from message m, recipient r limit 20;


Which returns in under 4 seconds. Am I doing something wrong with the 
string_agg query to make it hang?


Thanks in advance - I greatly appreciate any help you can offer.

jr


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

[GENERAL] Notify rule

[ivan_14_32]

Hi!
I trying to create rule for update notifing:

CREATE OR REPLACE RULE "ttt_NotifyU" AS
ON UPDATE TO "ttt" DO
 NOTIFY "ttt", '88';
this work.

But this
CREATE OR REPLACE RULE "ttt_NotifyU" AS
ON UPDATE TO "ttt" DO
 NOTIFY "ttt", NEW."id";
don't (syntax error),
NOTIFY "ttt", CAST(NEW."id" as text)
too.

Q: How can I send tuple id (integer primary key) via notify using Rule?
Target is erasing deprecated tuples from application's cache.

Re: [GENERAL] string_agg hanging?

[Pavel Stehule]

Hello

these queries are different. I checked a string_agg and it working
with me. So I am expecting, so your query has a performance problem
when aggregate function is used.

please, recheck so indexes are used
you can try "set enable_seqscan" to off, maybe set hashagg to off

please, send a execute plans - see statement EXPLAIN

Regards

Pavel Stehule

2011/2/21 Joel Reed :
> Hoping someone will be kind enough to share how to write a query that uses
> 9.0's string_agg with a subselect, something like...
>
> select m.id,m.subject,m.from_address,m.date_sent,m.size,string_agg((select
> address as to_address from recipient r inner join message_recipient mr on
> r.id=mr.recipient_id and mr.message_id=m.id and mr.recipient_type='To'), ',
> ') from message m, recipient r group by m.id, m.subject, m.from_address,
> m.date_sent, m.size limit 20;
>
> Unforunately, that just seems to hang. So I'm using...
>
> select
> m.id,m.subject,m.from_address,m.date_sent,m.size,array_to_string(ARRAY(select
> r.address from recipient r inner join message_recipient mr on
> r.id=mr.recipient_id and message_id=m.id and mr.recipient_type='To'), ',')
> as to_addresses from message m, recipient r limit 20;
>
> Which returns in under 4 seconds. Am I doing something wrong with the
> string_agg query to make it hang?
>
> Thanks in advance - I greatly appreciate any help you can offer.
>
> jr
>
>
> --
> 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