[GENERAL] UPDATE ... RETURNING atomicity
Hello, In this query: UPDATE foo SET allocated_to=? WHERE id=(SELECT MIN(id) FROM foo WHERE allocated_to IS NULL) AND allocated_to IS NULL RETURNING id Is it guaranteed in any way that there will only be one id allocated and returned even if multiple clients are executing this query concurrently? Or is there a possibility that some other client executing this query (or another query modifying allocated_to) might set allocated_to to non-NULL and commit right after the inner select finds it as NULL, so the outer "AND allocated_to IS NULL" will no longer be true, and the outer query will return nothing? 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] Full text search on a complex schema - a classic problem?
On 23/05/10 10:40, Ivan Voras wrote: Hello, I have a schema which tracks various pieces of information which would need to be globally searchable. If systems that exist outside the database its self are acceptable, check out Apache Lucerne, and tools that use it like Hibernate Search. -- Craig Ringer -- 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] Moving from Mysql
* Luis Daniel Lucio Quiroz (luis.daniel.lu...@gmail.com) wrote: > 1. whar are equivalent for these commands: > in mysql: mysqldump mydata_base_name pg_dump (pg_restore to restore from the dump, if you use a non-SQL format for it, which can give you the ability to do a parallel-restore) > mysql mydata_base_name < script.sql psql > 2. any link to read about how to admin pgsql with mysql backgraounds, The PG documentation is really quite good: http://www.postgresql.org/docs/8.4/ > 3. how users are managed in pgsql, i need to create a specifiq username for > db, > but how? PG Roles (users and groups) are managed on a per-cluster level. There isn't a really good way to do them at a per-database level today. A cluster in PG is a full PG instance and a single cluster contains multiple databases. You can manage which databases users are allowed to connect to though, check out the GRANT command. Thanks, Stephen signature.asc Description: Digital signature
[GENERAL] Full text search on a complex schema - a classic problem?
Hello, I have a schema which tracks various pieces of information which would need to be globally searchable. One approach I came up with to make all of the data searchable is to create a view made of UNION ALL queries that would integrate different tables into a common structure which could be uniformly queried by using tsearch2 functions. This would work, up to the point where it would be practically unavoidable (for performance reasons) to create indexes on this view, which cannot be done. I would like to avoid using a "hand-made" materialized view (via triggers, etc.) because of administrative overhead and because it would duplicate data, of which there is potentially a lot. I think this looks like a fairly common problem with full text searches on a large-ish schemas, so I'm wondering what are the best practices here, specifically with using tsearch2? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Moving from Mysql
Hi, Well, i'm moving some databases from Mysql to Pgsql, but i'm having some admin doubts, i wondering 3 things: 1. whar are equivalent for these commands: in mysql: mysqldump mydata_base_name mysql mydata_base_name < script.sql 2. any link to read about how to admin pgsql with mysql backgraounds, 3. how users are managed in pgsql, i need to create a specifiq username for db, but how? TIA LD -- 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] Select max(primary_key) taking a long time
Merlin Moncure writes: do a big delete recently? any other open transactions? Some inserts were taking place. Roughly 2 to 5 million rows inside transactions. We were doing some ETL and each batch represented a file we were loading. We need to have the entire file or roll back so each file is done within a transaction. the remedy for the former is to simply eat it (one time penalty) or rebuild the table. for the latter you simply have to resolve the other transaction. I think it is related to the inserts... after they were done everything was back to normal. how big is your table according to pg_relation_size()? \dt+ is easier. :-) 116GB -- 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] Alter column position
On Sat, May 22, 2010 at 11:01 AM, Patrick Rutkowski wrote: > Though I do see how the performance bumps could be useful, I would still bet > that the huge majority of users who utters the words "damn, I wish I could > re-order columns" are just talking about the visual order in psql(1) or > PgAdminIII. make a view :-) merlin -- 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] NOTICE: ignoring incomplete trigger group for constraint
On 22/05/2010 17:03, erobles wrote: > ERROR: there is no unique constraint matching given keys for referenced > table "table_name'" > > > there is a way to solve this?? what can i do ?? It means you need to have a primary key, or at least a unique constraint, on the target table which uses the column(s) which the foreign key references. For example: postgres=# create table a(f1 integer, f2 integer); CREATE TABLE postgres=# create table b(f3 integer, f4 integer); CREATE TABLE postgres=# alter table a add foreign key (f2) references b(f3); ERROR: there is no unique constraint matching given keys for referenced table "b" If I now add a primary key to table b, it works: postgres=# alter table b add primary key(f3); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "b_pkey" for table "b" ALTER TABLE postgres=# alter table a add foreign key (f2) references b(f3); ALTER TABLE HTH. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] NOTICE: ignoring incomplete trigger group for constraint
On 05/21/2010 11:18 AM, Tom Lane wrote: Yeah, this is known to happen in some cases where there was a broken (incompletely enforced) foreign key constraint in your old database. The odds are good that what you should do is nothing at all, because you probably didn't even realize you still had the FK constraint in the old database: the most common error cases weren't enforced. It's likely that if you try to add the FK constraint now, you'll find it fails because the data doesn't even satisfy the constraint. So you could just leave things alone and the new database will behave approximately like the old one did. But if you really want to add the FK constraint back in, ALTER TABLE ADD FOREIGN KEY is the way. hi, i made the alter table to add the foreign key, but in some constraints i have the follow error: ERROR: there is no unique constraint matching given keys for referenced table "table_name'" there is a way to solve this?? what can i do ?? regards, erobles -- 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] Select max(primary_key) taking a long time
On Fri, May 21, 2010 at 1:48 PM, Francisco Reyes wrote: > Tom Lane writes: > >> Francisco Reyes writes: >>> >>> I am trying to do >>> select max(primary_key) from some_table; >> >> Are there a whole lot of nulls in that column? > > > Zero nulls. It is a primary key. do a big delete recently? any other open transactions? since you are i/o waiting, I'm guessing either hint bits or you have another transaction in play which established tons of rows that your query has to wade through... the remedy for the former is to simply eat it (one time penalty) or rebuild the table. for the latter you simply have to resolve the other transaction. how big is your table according to pg_relation_size()? merlin -- 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] Help reqd on azimuth finction
On Sat, May 22, 2010 at 8:02 AM, Deepa Thulasidasan wrote: > Dear All, > > Can some one help me understand st_azimuth() available in postgis. did you read the documentation? (http://postgis.refractions.net/documentation/manual-svn/ST_Azimuth.html). the function calculates the angle between two points on a plane. merlin -- 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] Alter column position
Hmm, interesting. Though I do see how the performance bumps could be useful, I would still bet that the huge majority of users who utters the words "damn, I wish I could re-order columns" are just talking about the visual order in psql(1) or PgAdminIII. On May 22, 2010, at 10:49 AM, David Fetter wrote: > On Sat, May 22, 2010 at 09:34:50AM -0400, Patrick Rutkowski wrote: >> I'm curious, is there any latest word on this? >> >> (Note that I've read fully the link >> http://wiki.postgresql.org/wiki/Alter_column_position as well as all >> links stemming from it). > > No one's working on it, to my knowledge. While the "SELECT *" case > doesn't interest me too much, it would be part of the infrastructure > needed for PostgreSQL to optimize storage by placing all fixed-length > columns before any variable-length ones. > > Cheers, > David. > -- > David Fetter http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fet...@gmail.com > iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Alter column position
On Sat, May 22, 2010 at 09:34:50AM -0400, Patrick Rutkowski wrote: > I'm curious, is there any latest word on this? > > (Note that I've read fully the link > http://wiki.postgresql.org/wiki/Alter_column_position as well as all > links stemming from it). No one's working on it, to my knowledge. While the "SELECT *" case doesn't interest me too much, it would be part of the infrastructure needed for PostgreSQL to optimize storage by placing all fixed-length columns before any variable-length ones. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Alter column position
I'm curious, is there any latest word on this? (Note that I've read fully the link http://wiki.postgresql.org/wiki/Alter_column_position as well as all links stemming from it). I could go digging through the latest source code, or the mailing list Archives, but I get the feeling I'll really only get the latest info by asking here. Yesterday I forgot to put an auto-incremting "id" column for the usual pkey on a table I was creating (I was just really sleepy). So I did an ALTER TABLE and added to the end. But then seeing the "id" column at the end of the column list in psql and PgAdminIII just really messed with the aesthetic sensibilities in my brain. I know, I can do the work-arounds in the wiki, and in this case I just re-created the table since it was fresh and had no data, but I would still really love to be able to properly re-order things. I would love to do it myself, but I'm barely even proficient with writing front-end SQL, let alone working on the backend of a complicated server implementation. Dunno what good this email will do, just felt like writing a bit :-) -Patrick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help reqd on azimuth finction
Dear All, Can some one help me understand st_azimuth() available in postgis. Regards, Deepa. -- 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] copy data from one db into another via copy & psql
On 2010-05-21, Kevin Kempter wrote: > Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so > I can load the data into a table in the second db 'inline' without writing to > & reading from a flat file? Yes. COPY ... TO stdout; at the source and COPY ... FROM stdin; at the destination. I use this with the COPY ( query ) TO STDOUT ; syntax when I want to copy only a few rows. -- 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] Is postgres installed?
On 21/05/2010 9:43 PM, christophe.an...@elsys-design.com wrote: Hi, How I know whether Postgres is already installed or not on a machine(on Linux and Windows)? I found that pg_ctl --version could be used Only if PostgreSQL's binary directory is on the PATH, which it may not be especially on Windows. There's no reliable way to detect a PostgreSQL install on UNIX/Linux/BSD either. You can look for pg_ctl on the path and tell the user that the pg_ctl for the postgresql install they want to use simply must be on the path. However, some Linux flavours support parallel installation of multiple versions of PostgreSQL (and multiple clusters for a given version), and will *not* have pg_ctl on the path for versions of PostgreSQL installed on the system. On Ubuntu/Debian you'll only find `pg_ctlcluster', which is a pg_ctl wrapper that takes additional 'version' and 'cluster' arguments. On these systems you can use pg_lsclusters to find PostgreSQL clusters. Alternately, you could simply require the user to set up the PATH manually, eg PATH=/usr/lib/postgresql/8.4/bin:$PATH ./yourprogram which will work anywhere and everywhere there's a Pg install. or I also tried to check into the registry (for windows HKLM\SOFTWARE\PostgreSQL\Installations), however with Windows Server 2003 pg_ctl is not recognized (probably not in the path) and the registries are not written (nothing appears under HKLM\SOFTWARE\) How was Pg installed? Using which installer package? Here (Vista, XP and Windows 7) Pg appears under HKLM\Software\Postgresql\Installations\postgresql-8.4 as installed by the EnterpriseDB "one-click" installer. If you "installed" Pg from the zip file, there's no real way to figure out if it's installed except checking the PATH for pg_ctl.exe or checking the running process list for postgres.exe . Both are fallible - the former if pg_ctl isn't on the path, the latter if PostgreSQL is installed but not currently running. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
