Re: [GENERAL] [BUGS] compatibilty postgres 9.2 RHEL 6.4
On Thu, Mar 5, 2015 at 5:27 AM, Elena Fernandez Carmona wrote: > Hi, > > I have Postgres 9.2.4 installed on a RHEL 5.5 > > I am going to migrate the operating system version to RHEL 6.4, and I would > like to know if it is compatible with my postgres 9.2.4. > This is clearly *not* a bug so, i'm moving this thread to pgsql-general@postgresql.org. Please use that list for General questions, there is also a spanish language list if you want to use it: pgsql-es-ay...@postgresql.org > The only information I have found is on the following url: > > http://www.postgresql.org/docs/9.2/static/supported-platforms.html > > I would also like to know, in case the versions are compatible, if it is > necessary to relink the postgres libraries? > An interesting question is how you installed your postgresql, but probably will be problems because of dependency libraries. Now. the data directori *is* compatible if you keep using 9.2 so i would install the right binary from yum.postgresql.org and use existing data directory -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to speed up pg_trgm / gin index scan
On Mon, Jun 22, 2015 at 6:51 AM, Christian Ramseyer wrote:
>
> DM=# \d+ logs_01
>
> Column|Type |
> --+-+
> host | character varying(255) |
> facility | character varying(10) |
> priority | character varying(10) |
> tag | character varying(255) |
> log_date | timestamp without time zone |
> program | character varying(255) |
> msg | text|
> seq | bigint |
>
> Indexes:
> "logs_01_pkey" PRIMARY KEY, btree (seq)
> "idx_logs_01_lower_host" btree (lower(host::text) varchar_pattern_ops)
> "logs_01_date_index" btree (log_date)
> "tridx_logs_01_msg" gin (msg gin_trgm_ops)
>
>
> DM=# select count(*) from logs_01;
> count
> --
> 83052864
>
>
> I'd like to provide a fast "like %x%" search on the msg column, hence I added
> a trigram based gin index on it. It is around 60 GB on the 35 GB table:
>
> DM=# select count(*) from logs_01;
> count
> --
> 83052864
>
>
> DM=# \dt+ logs_01
> List of relations
> Schema | Name | Type | Owner | Size | Description
> +-+---+--+---+-
> public | logs_01 | table | postgres | 35 GB |
>
> DM=# \di+ tridx_logs_01_msg
>List of relations
> Schema | Name| Type | Owner | Table | Size | Description
> +---+---+--+-+---+-
> public | tridx_logs_01_msg | index | postgres | logs_01 | 58 GB |
>
>
What version of postgres is this? GIN indexes improved a lot in 9.4,
they use less than half the space and have doubled the speed (on
average).
Now, whatever version you have; a GIN index has two data structures,
the main one in which the index entries are stored as key-value pairs
(please someone correct my description of the situation) and a pending
list, which is a temporary unsorted list of pending entries in which
all the newly inserted tuples arrive until a VACUUM (or until the
pending list grows upto work_mem) moves that list into the main
structure.
That happens to avoid the penalty of inserting new rows in the main
structure which could be expensive.
But while the pending list grows the speed of the index decreases. And
because you have work_mem in 16Gb your pending list is possibly
growing without control.
if you have 9.3 or superior you can know how big is that pending list
installing pgstattuple.
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatginindex('tridx_logs_01_msg');
NOTE: remember that pending_pages is expressed in 8kb-pages
if that is the problem or if you are in <= 9.2 then try VACUUM the table
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
--
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] Transactional DDL
On 6/2/07, PFC <[EMAIL PROTECTED]> wrote: >> This is what happens in every RDBMS. Whats so special about postgres >> then? mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE ble ( id INTEGER ) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO ble VALUES (1),(2),(3); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM ble; +--+ | id | +--+ |1 | |2 | |3 | +--+ 3 rows in set (0.00 sec) Tom's example seems to show that mysql inserts a commit immidiatelly after a DDL but this one example shows the thing is worse than that. if that is the case this 3 rows should have been gone with the rollback. -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Transactional DDL
> On 6/2/07, *Jasbinder Singh Bali* <[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]>> wrote: > > On 6/2/07, *Michael Glaesemann* < [EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]>> wrote: > > On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote: > > > Whats so novel about postgresql here? > > This would happen in any RDBMS. right? > > You induced divide by zero exception that crashed the whole > > transaction and it did not create the table bar? > No, it doesn't then informix is better than oracle in this point. last time i try this on informix it did the right thing... sadly enough, i don't have an informix database at hand to confirm if my memory has no corrupted indexes ;) -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] automatically execute a function each day
On 6/10/07, Ottavio Campana <[EMAIL PROTECTED]> wrote: I want to execute a function automatically every day, let's say at midnight. Can I do it directly in postgresql, or do I have to use some external programs (cron?) ? Thanks. you have to use an external program (cron is good) -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Add Column BEFORE/AFTER another column
On 8/24/07, Robert Treat <[EMAIL PROTECTED]> wrote: > On Friday 24 August 2007 17:18, Matthew wrote: > > Hey Bill, > > > > > It does not. > > > > Bummer. > > > > > To get your columns in a specific order, specify the column names in > > > that order in your SELECT statement. The SQL standard doesn't provide > > > for any other way to guarantee column order, and neither does Postgres. > > > > Yes, I realize this and we do identify our columns during select > > statements, but when you look at a table using a tool like phpPGAdmin or > > pgAdmin3, the columns are displayed in some defined order. It's much > > easier to see your data/columns in some logical order (ie: all the cost > > columns next to each other). > > > > FWIW (and it isnt much) we had the semblence of a patch and an actual plan for > implementing this, but no one got interested enough to finish it for 8.3. > Should you happen to know an ambituous C hacker, there is a good chance it > could be included in 8.4. > really? i don't remember it? can you point me where that patch is?, i can't find it, either -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Can this function be declared IMMUTABLE?
On 8/27/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Well, I am considering a function that does read from a table, but the > table contents change extremely infrequently (the table is practically a > list of constants). Would it be safe to declare the function IMMUTABLE > provided that the table itself is endowed with a trigger that will drop > and recreate the function any time the table contents are modified? In > this way, it seems that the database would gain the performance benefit of > an immutable function for the long stretches of time in between changes to > the table. > make the function STABLE instead > I apologize that I don't have any details -- it is still very early in the > development of the database design, and I was just hoping to get a better > understanding of whether an immutable function would safely offer any > benefit in this scenario. > do you know that early optimization is the root of all evil? -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Max File size per Table (1G limit??)
On 9/3/07, Ow Mun Heng <[EMAIL PROTECTED]> wrote: > I just browsed to my $PGDATA location and noticed that there are some > tables which has ending of .1 > > # ls -lahS | egrep '(24694|24702|24926)' > -rw--- 1 postgres postgres 1.0G Sep 3 22:56 24694 > -rw--- 1 postgres postgres 1.0G Sep 3 22:52 24702 > -rw--- 1 postgres postgres 1.0G Sep 3 22:58 24926 > -rw--- 1 postgres postgres 800M Sep 3 22:57 24694.1 > -rw--- 1 postgres postgres 161M Sep 3 22:52 24702.1 > -rw--- 1 postgres postgres 12M Sep 3 22:58 24926.1 > > I'm wondering what are these since I've not set up table partitioning > just yet. > > postgres uses datafiles from up to 1GB, if a table has more data than that limit then postgres creates more files. http://www.postgresql.org/docs/8.2/static/storage-file-layout.html -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] AUXILIO!!!! CONSULTA SOBRE CURSORES HELP!!! ABOUT CURSORS
On 9/24/07, genesis <[EMAIL PROTECTED]> wrote: > Amigos tengo el siguiente problema, soy nuevo en postgres y no > comprendo muy bien, bueno en realidad para nada como hacer > procedimientos almacenados en postgres que me devuelvan un dataset > > Friends, I'm new in postgress and dont know at all anything about > stored procedures in postgresql, > can u help me and teach me how i can get a cursor for a dataset? > http://www.postgresql.org/docs/current/static/plpgsql-cursors.html http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING espero que te sirva - hope it helps you -- Atentamente, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] publicar
On Jan 21, 2008 5:38 PM, Henrry Joshney Servitá Sánchez <[EMAIL PROTECTED]> wrote: > como hago para publicar un post? > > creo que ya lo hiciste, basicamente escribiendo un mail a la direccion de la lista... ahora, esta es una lista en ingles... asi que o escribes en ingles o envia los mensajes a la lista de la comunidad hispana ([EMAIL PROTECTED]) i think you already did it, just write a mail to the list address... but, this is an english list... so write in english or better write to the spanish list ([EMAIL PROTECTED]) -- Atentamente, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] match accented chars with ASCII-normalised version
On Jan 25, 2008 12:02 AM, brian <[EMAIL PROTECTED]> wrote:
> The client for a web application I'm working on wants certain URLs to
> contain the full names of members ("SEO-friendly" links). Scripts would
> search on, say, a member directory entry based on the name of the
> member, rather than the row ID. I can easily join first & last names
> with an underscore (and split on that later) and replace spaces with +,
> etc. But many of the names contain multibyte characters and so the URLs
> would become URL-encoded, eg:
>
> Adelina España -> Adelina_Espa%C3%B1a
>
> The client won't like this (and neither will I).
>
> I can create a conversion array to replace certain characters with
> 'normal' ones:
>
> Adelina_Espana
>
> However, I then run into the problem of trying to match 'Espana' to
> 'España'. Searching online, I found a few ideas (soundex, intuitive
> fuzzy something-or-other) but mostly they seem like overkill for this
> application.
>
what about using to_ascii() ?
http://www.postgresql.org/docs/8.3/static/functions-string.html
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Copying data files to new hardware?
On Wed, Oct 13, 2010 at 11:03 AM, EDH wrote: [...] > > What I'd like to know is: if I install the latest 8.2.x release - I > see 8.2.18 RPMs are available - can I do a straight copy of the > contents of /var/lib/pgsql/data/ to the new server and start it up? > Or is dump & restore the only real way to do this? > it's safe to just install any version of 8.2.x, copy the whole data directory (plus any tablespace's directories you could have) and start again... probably you want to REINDEX your indexes (read the release notes to see what specific kind of indexes you need to reindex) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] School teacher in need of HELP
On Sat, Mar 5, 2011 at 8:04 PM, Adrian Klaver wrote: > > How did you install Postgres? i bet he uses one click installer from enterprisedb, i have seen those messages he describe... > My guess is that the database was set with trust authentication, this means > you > do not have to supply a password. Try the command without a password. > right. and because the OP is a good reader he should read chapter 19.1 (http://www.postgresql.org/docs/9.0/static/auth-pg-hba-conf.html) and use trust in authentication method -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] shared folder in Hot Standby
On Mon, Apr 4, 2011 at 12:35 AM, Shoaib Mir wrote: > From my limited knowledge I think we need a shared location where the master > node is putting the WAL files in and the slave nodes also look at the same > folder to get new WAL logs to replay the files. Now if we cant have that > shared location and a scenario where all slaves and the master cant see one > shared location, how to approach this? no. while you can do that, it's optional... you can setup a hot standby just by setting: on master: === postgresql.conf === wal_level=hot_standby archive_mode=on archive_command='exit 0' max_wal_senders = on slave: === postgresql.conf === hot_standby=on === recovery.conf === standby_mode='on' primary_conninfo = 'host=master_ip' or you can use repmgr to make all this easier: http://projects.2ndquadrant.com/repmgr -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] [PERFORM] Very slow inner join query Unacceptable latency.
On Tue, May 21, 2013 at 4:53 PM, wrote: > The SARS_ACTS table currently has 37,115,515 rows > > we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree > (sars_run_id) > we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIMARY > KEY (id ) > > serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join > SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1_.ALGORITHM='SMAT'; can you please show us an EXPLAIN ANALYZE of this query (not only EXPLAIN). please paste it in a file and attach it so it doesn't get reformatted by the mail client. what version of postgres is this? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- 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] [PERFORM] Very slow inner join query Unacceptable latency.
On Thu, May 23, 2013 at 12:21 PM, wrote: > > But what negative impact is disabling hash joins? > doing it just for a single query, could be a tool for solving particular problems. setting it in postgresql.conf, therefore affecting all queries, is like using a hammer to change tv channel... it will cause more problems than the one it solves. what you can do is: 1) execute: SET enable_hashjoin TO OFF; SELECT here RESET enable_hashjoin TO ON; 2) in a function: CREATE FUNCTION do_something() RETURNS bigint AS $$ SELECT here $$ LANGUAGE sql SET enable_hashjoin TO OFF STABLE; -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- 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] temp sequence
On Feb 3, 2008 1:39 AM, Sim Zacks <[EMAIL PROTECTED]> wrote:
> "PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC i386-pc-linux-gnu-gcc
> (GCC) 4.1.1 (Gentoo 4.1.1)"
>
> I am creating a temporary sequence in a function and it seems like it is not
> going away after the function finishes.
> The front end is in MS Access 2000 and I have a single connection. When I call
> the function once it works, when I call it a second time, it gives me an error
> that the sequence already exists. When I restart the application, I can call
> the
> function again.
>
create, and use the sequence through EXECUTE
EXECUTE 'create temp sequence seq1';
EXECUTE 'select nextval(' || quote_literal('seq') || ') ';
the same apply for all kind of temp objects, in 8.3 this no longer
will be an issue
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] INFINITE RECURSION with rules...
On Sun, Mar 23, 2008 at 10:30 AM, srdjan <[EMAIL PROTECTED]> wrote:
[...]
> CREATE TABLE b (id smallint PRIMARY KEY, email_a varchar(20), name_a
> varchar(10), tot smallint, FOREIGN KEY (email_a, name_a) REFERENCES a(email,
> name));
>
[...]
>
> -- And this easy rule
>
> CREATE RULE rrr_a_b AS ON INSERT TO b
> DO INSTEAD
> INSERT INTO b VALUES
> (NEW.id,
> NEW.email_a,
> NEW.name_a,
> (SELECT calc(NEW.email_a, NEW.name_a))
> );
>
> -- Sample for insert into b
>
> INSERT INTO b VALUES (33,'[EMAIL PROTECTED]','bill');
>
[...]
> Trying to insert into b (and using the new rule defined by myself, i receive
> this message:
>
> ERROR: infinite recursion detected in rules for relation "b"
>
when you insert into b the rule rewrites your query into an insert
into b... ah... another insert into b, the rule rewrites *again* the
query into (guess what?) another insert into b... and the rule system
will continue rewriting your query until it get something different to
an insert into b... hope i was clear...
now, why the rule? isn't enough to simply do this?
INSERT INTO b VALUES (33,'[EMAIL PROTECTED]','bill',
calc('[EMAIL PROTECTED]', 'bill'));
or maybe using a trigger before insert but you're insert should look like:
INSERT INTO b(id, email_a, name_a) VALUES (33,'[EMAIL PROTECTED]','bill');
and in the trigger fill the tot column
--
regards,
Jaime Casanova
-
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] temporarily deactivate an index
On Sun, Jun 8, 2008 at 1:34 AM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Sat, Jun 7, 2008 at 5:16 PM, Viktor Rosenfeld > <[EMAIL PROTECTED]> wrote: >>> >>> Try this: >>> >>> begin; >>> drop indexname; >>> explain analyze select ...; >>> rollback; >> >> That works, but I'm still looking for another way to deactivate the index. >> The reason being, that my query load is randomly generated by a Java >> program and I don't want to go and change the SQL compiler. > > Sorry, I'm out of ideas. I mean, you can turn off all indexes with > set enable_indexscan=off but there's no other way to do it cheaply and > in such a fine grained way. > with "enable_indexscan=off" you're not turning indexes off but putting a high cost in using them... the effect, most of the time, is that indexes will not be used but you can't be sure... the better solution was the first one: dropping the index inside a transaction, execute the explain analyze of the query and rollback the transaction -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] temporarily deactivate an index
On Sun, Jun 8, 2008 at 11:45 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > Viktor Rosenfeld <[EMAIL PROTECTED]> writes: >> That works, but I'm still looking for another way to deactivate the >> index. The reason being, that my query load is randomly generated by >> a Java program and I don't want to go and change the SQL compiler. > > Well, you're going to have to change *something* on the client side, > assuming you want this behavioral change to affect only some queries. > > Otherwise, in versions that have CREATE INDEX CONCURRENTLY you could > mark the index not ready to use yet (see pg_index.indisvalid or > pg_index.indisready depending on version). > if i mark the index not ready (using pg_index.indisvalid or pg_index.indisready depending on version), will the index be updated if in another transaction i make an insert? -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join
On Tue, Jun 17, 2008 at 1:25 AM, m laks <[EMAIL PROTECTED]> wrote: > Hi, > I am migrating to postgresql 8.1 from 7.4 with debian. > [...] > > the following worked before: > > LTA_IDB=# delete from instancetable where ( (imagelevel.serparent= > '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= > imagelevel.sopinsuid));(at least in postgresql 7.4 using perl DBI). > > It worked on postgresql 7.4 and now on postgresql 8.1 I get error: > > ERROR: missing FROM-clause entry for table "imagelevel" > maybe the add_missing_from parameter in postgresql.conf is what you need to get some sleep :) it's there for older application like yours -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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 interpreting pg_stat_bgwriter output
On Thu, Aug 13, 2009 at 3:00 AM, Greg Smith wrote: > >> buffers_backend = 740 > > This number represents the behavior the background writer is trying to > prevent--backends having to clean their own buffers up. > so what we want on busy systems is buffers_backend to be (at least) equal or (better) lower than buffers_clean, rigth? or i'm understanding wrong? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] auto truncate/vacuum full
On Tue, Oct 27, 2009 at 6:29 PM, Alvaro Herrera wrote: > > Do you have a vacuum in cron or something like that? As Tom says, if it > had been autovacuum, it should have been cancelled automatically (else > we've got a bug); but something invoking vacuum externally wouldn't > have, so what you describe is what we would expect. > then we have a bug (at least in 8.3, haven't tried in 8.4)... i see this a month ago, an autovacuum blocking a lot of concurrent updates and selects... once i pg_cancel_backend() the autovacuum process the other ones starting to move -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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 replication question :- One master 2 slaves 9.0.10
On Tue, Oct 1, 2013 at 5:46 PM, Sergey Konoplev wrote: > On Tue, Oct 1, 2013 at 2:03 PM, akp geek wrote: >> One more thing.. pardon me for being dumb >> >> I want to set the 2 nd slave as HOT STAND BY, not steaming .. > > Hot standby assumes being streaming. You can not establish a hot > standby without using streaming replication. What is the reason not to > do it streaming? > Ah! why? you don't need to use streaming replication for a hot standby, it works perfectly well even if you replay everything from archive and never do streaming. but it would be a good idea to set hot_standby_feedback to on and max_standby_archive_delay to something larger than 30s -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- 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 replication question :- One master 2 slaves 9.0.10
On Tue, Oct 1, 2013 at 10:48 PM, Sergey Konoplev wrote: > On Tue, Oct 1, 2013 at 6:15 PM, Jaime Casanova wrote: > >> but it would be a good idea to set hot_standby_feedback to on and >> max_standby_archive_delay to something larger than 30s > > Doesn't replica need a connection to master for hot_standby_feedback? > doh! yes, it needs it... vacuum_defer_cleanup_age it's the one you should set if never do streaming... but, of course, that is not accurate enough -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Comparing Oracle vs Postgres Fwd: [ADMIN] Database replication to 50-80 nodes
-- Forwarded message -- > From: Shahzad Bashir > Date: Thu, Oct 3, 2013 at 5:43 AM > Subject: RE: [ADMIN] Database replication to 50-80 nodes > To: Jaime Casanova > > > hi, > I am very much new to postgres, infact just started... can anyone please share > whats and what not of postgres in comparison with oracle . Hi, Please, when you do a new question send a new mail not just a reply from an existing question. This is important because these mails are archived so people can search later for when similar issues appear. Also, please use "reply all" so you can be sure your question arrives to the list and not one person. > Secondly is there > ":database link " in postgres and if not , what is the alternative of it. > what version of postgres is this? starting in 9.3 you can create foreign tables between postgres databases, before that you can create a foreing server and use the functions from th dblink module to connect to that server and query the table on it -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- 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] Comparing Oracle vs Postgres Fwd: [ADMIN] Database replication to 50-80 nodes
On Thu, Oct 3, 2013 at 5:40 PM, Ian Lawrence Barwick wrote: > 2013/10/4 Jaime Casanova : >> what version of postgres is this? starting in 9.3 you can create >> foreign tables between postgres databases, before that you can create >> a foreing server and use the functions from th dblink module to >> connect to that server and query the table on it > > Read-only foreign tables have been available since 9.1, it's writeable foreign > tables which were introduced in 9.3. > and the postgres_fdw -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- 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] [HACKERS] PostgreSQL Core Team
On Wed, Apr 27, 2011 at 1:48 PM, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > Congratulations! -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] Remove Modifiers on Table
On Tue, May 17, 2011 at 10:14 AM, Carlos Mennens wrote: > > Yes that worked perfect! I'm just curious if I have 20 tables and then > want all the 'id' columns to be auto incrementing , that means I have > to have 20 listed sequences for all 20 unique tables? yes > Seems very > cluttered and messy for PostgreSQL. Can one sequence be attributed to > multiple columns in multiple tables? you can use only one sequence for all yes... but then you will have id=1 in one table, id=2 in another, etc... i mean, it will generate one single list of values for all tables > I'm used to MySQL where this was > as easy as running: > > CREATE TABLE test ( > id INT PRIMARY KEY AUTO INCREMENT); > in postgres is as easy as CREATE TABLE test( id SERIAL PRIMARY KEY); hey! it's even less keystrokes! -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] Query to return every 1st Sat of a month between two dates
On Wed, May 11, 2011 at 10:22 AM, Alex - wrote:
> Hi,
> is there an easy way to return the date of every first Saturday of a month
> in a data range i.e. 2011-2013
if you want a list of the first saturdays of every month and you're
using at least 8.4:
with q as (select d, lag(d) over ()
from generate_series('2011-02-01'::date, now()::date, '1
day') as s(d)
where extract(dow from d) = 6
)
select d from q where (lag is null) or (extract(month from d) <>
extract(month from lag));
where '2011-02-01' is the initial date and now()::date - '1 day' the
final one, replace them with you're own range
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to check a table content efficiently? With LIMIT and OFFSET?
On Sun, May 29, 2011 at 4:55 PM, Stefan Keller wrote: > >>> 2. There's an autovacuum background process which already does the >>> job, doesn't it? >> >> Yes, but in its own time. If you know there has been a batch of >> inserts/deletes you might as well run analyse immediately on that table. > > My table is a read-only table after all. > That's another reason why I'm reluctant using ANALYZE . > sorry, i don't follow that... why do you think that a read-only table doesn't need an ANALYZE? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] insert a SYSTIMESTAMP value in postgres
El 20/06/2011 14:33, "Leon Match" escribió: > > Hello, > > > > I am trying to re-create few objects from oracle into postgres. > > > > I have a problem inserting a timestamp value into the table: > > > > insert into request_queue (request_id, received_time > > ) > > values (new.request_id, SYSTIMESTAMP > > ); > > > > How can I insert a dynamic timestamp value in postgress, please? > Maybe this document can help you http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion -- Jaime Casanovawww.2ndQuadrant.com
Re: [GENERAL] [BUGS] Where is pg_create_restore_point funciton in 9.1a2 ?
On Wed, Jun 22, 2011 at 3:28 AM, Emanuel Calvo wrote:
> I'm still finding pg_create_restore_point in 9.1a2 documentation:
> http://www.postgresql.org/docs/9.1/static/functions-admin.html
>
> But I've compiled that version and I didn't found it:
>
> postgres=# \df *create_restore*
> List of functions
> Schema | Name | Result data type | Argument data types | Type
> +--+--+-+--
> (0 rows)
>
the function exists... and it should appear, it does for me
postgres=# \df *create_re*
List of functions
Schema | Name | Result data type | Argument
data types | Type
+-+--+-+
pg_catalog | pg_create_restore_point | text | text
| normal
(1 row)
postgres=# select pg_create_restore_point('jcm');
ERROR: WAL level not sufficient for creating a restore point
HINT: wal_level must be set to "archive" or "hot_standby" at server start.
STATEMENT: select pg_create_restore_point('jcm');
>
> By the way, another issue that I found is when I execute \df. It
> doesn't display anything (I must
> force with * to do that).
i can confirm this
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
--
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] repmgr problem with registering standby
On Wed, Jul 27, 2011 at 4:36 AM, Toby Corkindale wrote: > > So that looks good, but then I try this on the slave: > # repmgr -f /etc/repmgr/validator/repmgr.conf \ > --verbose standby register > can you show the content of /etc/repmgr/validator/repmgr.conf? [...] > > I can query the database like so though, and it seems like it's all good: > repmgr=# select * from repmgr_validator.repl_nodes; > id | cluster | conninfo > +---+-- > 0 | validator | host=10.133.54.2 port=5432 user=repmgr dbname=repmgr > (1 row) > this is on the master or the slave? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] repmgr and archive_mode
On Fri, Jul 29, 2011 at 3:12 AM, Toby Corkindale wrote: > Hi, > In the repmgr README, it suggests configuration should include: > > archive_mode = on > archive_command = 'cd .' > > Could someone explain why we need archive_mode enabled? In my own testing of > Pg's streaming replication, I've found it to work without this enabled. > it's not really needed but in some situations you could want to archive wal segments (for example if you want to shut down the standby without having to put a really big value in wal_keep_segments) and while archive_command can be changed at anytime just reloading archive_mode needs to restart the server. so this is just a good configuration for 24x7 setups -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] eval function
2011/7/30 Sim Zacks : > > On 07/28/2011 05:11 PM, Tom Lane wrote: > >> Merlin Moncure writes: >>> >>> Couple points: >>> *) why a special case for boolean values? >> >> That seemed weird to me too ... > > I'm using 8.2 and "cannot cast type boolean to text" are you aware that support for 8.2 will stop in December 2011, right? http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] repmgr problem with registering standby
On Wed, Jul 27, 2011 at 7:24 PM, Toby Corkindale wrote: > On 28/07/11 03:47, Jaime Casanova wrote: >> >> On Wed, Jul 27, 2011 at 4:36 AM, Toby Corkindale >> wrote: >>> >>> So that looks good, but then I try this on the slave: >>> # repmgr -f /etc/repmgr/validator/repmgr.conf \ >>> --verbose standby register >>> >> can you show the content of /etc/repmgr/validator/repmgr.conf? > > cluster=validator > node=mel-db06 > conninfo='host=10.133.54.1 port=5432 user=repmgr dbname=repmgr' > sorry for the delay on this... do you still have this problem? the node parameter should be an integer value, i don't think that string should work for you >>> I can query the database like so though, and it seems like it's all good: >>> repmgr=# select * from repmgr_validator.repl_nodes; >>> id | cluster | conninfo >>> +---+-- >>> 0 | validator | host=10.133.54.2 port=5432 user=repmgr dbname=repmgr >>> (1 row) >> if in the standby that string you're using as node value ends up as a 0 then it never asks for the node 0 (it couldn't be the master because you're just registering as a standby) so i bet that's the problem, use numbers in the node parameter and everything will be ok i will have to add a check against this case in repmgr, though -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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 8.0 upgrade to 9.0
On Mon, Aug 1, 2011 at 9:12 AM, Atul Goel wrote: > Hi Forum, > > We are planning to upgrade a postgres 8.0 database to postgres 9.0 (Actually > already done in Dev). consider that 9.0 is not the next version after 8.0, there were 4 more (8.1, 8.2, 8.3 and 8.4) and at least for changing from 8.2 to 8.3 you probably will need to fix your app > The application is J2EE application with Hibernate. My > question are > > 1) Is there a list of things that needs to be taken care while > upgrading(known issues). > they are all mentioned in realese notes... look the "Migration to Version X.X" in the release notes for the above mentioned versions > 2) Do I need to upgrade JDBC driver when I upgrade to postgres9.0. > probably but i'm not so sure about it -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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 table have a large number of relpages and occupied a big memory size
On Fri, Aug 5, 2011 at 2:37 AM, Vivekkumar Pandey wrote: > > Hi Tomas, > > I am using the slony cluster and both the database have the same Data. > > So Please provide the appropriate solution > > On Fri, Aug 5, 2011 at 12:47 PM, Tomas Vondra wrote: > > > > That suggests you're using something else to build the cluster (e.g. slony > > or something like that). In that case the size difference may be simply > > due to data differences or dead tuples. VACUUM FULL should compact the > > dead tuples, but it's not a cheap command (takes exclusive locks, time and > > memory). > > It seems like Tomas gives you the solution (at least part of it): use VACUUM FULL to compact your data on the master. Also, probably you want to revisiti your autovacuum's configuration. Finally, remember that Slony has two tables that logs all changes in the database... normally only one of the table should be in use while Slony is processing the queu of the other and truncate it. but if the slon process are not running those tables start to grow... can you check that the slon processes are running -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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 table have a large number of relpages and occupied a big memory size
On Fri, Aug 5, 2011 at 5:26 AM, Tomas Vondra wrote: > On 5 Srpen 2011, 10:52, Vivekkumar Pandey wrote: > >> Also suggest the Query that can view the dead tuples in the table. > > You can't see the dead tuples with a query - that's why they're called > dead. you can see an estimate of how many dead tuples are looking at the n_dead_tup in pg_stat_all_tables -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] about synchronous_standby_names or sync replic
On Thu, Sep 22, 2011 at 2:34 PM, e-blokos wrote: > Hi Folks, > > I wonder if it's possible to have a sync replication only from the the node > where the client > who update the DB and other node replicated asynchronously ? > > example : > nodeCentral node1 node2 node3 > > user1 from node3 update db to nodeCentral (master) so nodeCentral > replicates synchronously node3 and later other nodes... > yep, put the names of all the nodes in synchronous_standby_names and set synchronous_commit to local... then when someone sends a write command to the server "set synchronous_commit to on" (of course, this SET is a command you send to the server just like any other SQL command) don't forget to reset synchronous_commit or use set local, otherwise you will let this node as the sync rep until disconnect -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] Promoting sync slave to master without incrementing timeline counter?
On Thu, Jun 21, 2012 at 10:10 AM, David Pirotte wrote: > > 2ndQuadrant's repmgr uses the second option so that the async slave can > "follow" the new master, saving you from having to do a new base backup. > Additionally, the old master is able to start streaming replication from the > new master without a new base backup. (Repmgr does not actually support the > latter behavior out of the box, but it seemed to work.) > is not safe to make old master to start SR from new master without any additional action. if the old master crashed/disconnected before some info was sent to the slave, then the old master has info not in the slave so when it converts in new master that piece of info is lost... if now the old master tries to connect to the new master he will except that info to exists... > So, given a hard failure (i.e. power loss) of the master, `pg_ctl promote` > provides availability more quickly, but `pg_ctl restart` provides data > redundancy more quickly. Is this an accurate assessment of the tradeoffs > between the two approaches? yes, i think that's pretty much the difference > Are there risks associated with the `pg_ctl > restart` approach, or is it safe to use? > it's safe as long as you let repmgr do it ;) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL limitations question
On Thu, Jul 12, 2012 at 8:53 AM, Adrian Klaver wrote: > On 07/12/2012 06:44 AM, Tom Lane wrote: >> >> Adrian Klaver writes: >>> >>> If a table has a maximum size and rows have size then at some point you >>> will reach a limit on number of rows per table. >> >> >> I think the "unlimited" should be read as "you'll hit some other limit >> first". For example, I trust no one would read that line as implying >> that we can store more data than will fit on the machine's disks. >> In the same way, it's not meant to suggest that the number of rows isn't >> effectively limited by the max table size. > > > I would agree, but the OPs question was: > > " > My question is: > how is it possible to *reach* unlimited rows in table? > " > and then you can have "partitioned" tables, while the system consider them almost independent tables (they are dependent only in the sense that if you read the parent it will read the childs too) but for the user they will look as one single table. so even say see limited by table size is not that true from certain point of view maybe: limited by other constraints (or some other words to say that) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to read the execution Plan
On Thu, Apr 22, 2010 at 7:43 AM, akp geek wrote: > Hi all - > I would request, If any one has document on how to read and > interpret the postgres execution plan, can you please share it? http://wiki.postgresql.org/wiki/Introduction_to_VACUUM%2C_ANALYZE%2C_EXPLAIN%2C_and_COUNT http://wiki.postgresql.org/wiki/Using_EXPLAIN -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Performance and Clustering
On Wed, Apr 28, 2010 at 8:08 PM, Jaime Rodriguez wrote: > hi, > Today is my first day looking at PostgreSQL > I am looking to migrate a MS SQL DB to PostgreSQL :) :) > My customer requires that DBMS shall support 4000 simultaneous requests and that requests come from the fantasy of some one or are there numbers supporting that? if the app is correctly written the connections wiil be taken and relesead as needed then you can use a connection pooler -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] autovacuum: 50% iowait for hours
On Fri, May 14, 2010 at 5:59 PM, Joao Ferreira gmail wrote: > > For several reasons I can _not_ upgrade pg. I must use 8.1.4 and just > live with it. And learn to work around the issues it might bring me. > Additionally I can not change the cheap storage we ship in some lower > end versions of our product. > why you can't upgrade to say: 8.1.20... it has the same behaviour as 8.1.4 and less bugs... -- Jaime Casanova www.2ndQuadrant.com Soporte y capacitación de PostgreSQL -- 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] Commit every N rows in PL/pgsql
On Wed, May 26, 2010 at 1:27 AM, Len Walter wrote: > > PL/pgsql doesn't allow that because it doesn't support nested transactions. > Is there an equivalent Postgres way of doing this? what about this? create function f() returns void as $$ declare r record; begin for r in select col_a from t loop update t set col_c = col_a + col_b where col_a = r.a; end loop; return; end; $$ language plpgsql; -- Jaime Casanova www.2ndQuadrant.com Soporte y capacitación de PostgreSQL -- 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] 3rd time is a charm.....right sibling is not next child crash.
On Tue, Jun 8, 2010 at 12:49 PM, Jeff Amiel wrote: > > Does Slony manage it's own vacuuming separate from postgres' autovacuum? > Yes it does: http://www.slony.info/documentation/maintenance.html -- Jaime Casanova www.2ndQuadrant.com Soporte y capacitación de PostgreSQL -- 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] non-WAL btree?
On Fri, Aug 1, 2008 at 3:32 PM, Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > Isn't hash indexing implementation non-WAL ? > yes, but that's because no one thinks is worth the effort of making them WAL logged while they keep slower than btree... -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] non-WAL btree?
On Fri, Aug 1, 2008 at 3:36 PM, Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > It's not that I expect a lot of improvement by having non-WAL > indexing, it just sounds logical to me to have that, since > index can be re-created fast enough during recovery, and why you think that? if they are non WAL logged the only way to re-create them after a recovery is with a REINDEX... dropping the index and create after the bulk is just the same, i think... -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] non-WAL btree?
On Fri, Aug 1, 2008 at 4:49 PM, Alex Vinogradovs <[EMAIL PROTECTED]> wrote: > It's all about number of repetions. If say I load my table > with 50k every minute, and run reindex every minute, how > long do you think it would take by end of the day, when > my table (it's daily partition actually) is at maximum > capacity ? And database may actually never crash, and > I won't have to run reindex at all ;) > maybe http://www.postgresql.org/docs/8.3/static/wal-async-commit.html is what you need... begin; set local synchronous_commit to off; insert... insert... ... commit; or set synchronous_commit to off; copy command; set synchronous_commit to on; -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] What happen to the VARATT_SIZEP macro in version 8.3?
On 8/5/08, Don Pannese <[EMAIL PROTECTED]> wrote: > > I have C code which defines some user defined postgres functions. This code > has been used with Postgres version 7.4 and it uses the VARATT_SIZEP macro. > seems that macro was deprecated in 8.3... this is the commit that removed it: http://archives.postgresql.org/pgsql-committers/2007-02/msg00517.php The new comment says: ! * TOASTed. Generally, only the code closely associated with TOAST logic ! * should mess directly with struct varattrib or use the VARATT_FOO macros. Why you need that macro at all? Now, of you really need it maybe you can make your own wrapper in ine of your includes: #define VARATT_SIZEP(_PTR) \ VARATT_SIZEP_DEPRECATED(PTR) -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] Initdb problem on debian mips cobalt: Bus error
On 8/6/08, Glyn Astill <[EMAIL PROTECTED]> wrote: > > Mind you, I'd not especially recommend trying to run CVS HEAD for > > production purposes, but it would be real interesting at this point > > to see if you can compile it and run the regression tests with the > > toolchain you've got. > > > > I've no problem doing that, this machine is a toy not a production system. > > I've had little contact with CVS. Should I follow something at the following > URL, or should I go elsewhere? > > http://wiki.postgresql.org/wiki/Working_with_CVS > that is for setup your local copy of the repository, you should only need this: http://www.postgresql.org/docs/8.3/static/anoncvs.html -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Guayaquil - Ecuador Cel. (593) 87171157 -- 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] explain inside begin; commit;
On Mon, Aug 18, 2008 at 3:01 PM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > this: > > begin; > explain select * from catalog_items limit 5; > commit; > > return this: > > -- Executing query: > begin; > explain select * from catalog_items limit 5; > commit; > Query result with 2 rows discarded. > > > Query returned successfully with no result in 58 ms. > don't do it in pgAdmin but in psql -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- 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] Vaccuum best practice: cronjob or autovaccuum?
On Thu, Aug 28, 2008 at 9:47 AM, Joao Ferreira gmail <[EMAIL PROTECTED]> wrote: > > http://www.postgresql.org/docs/8.3/static/maintenance.html > > you'll find that once in a while (start at once/week and build up or > down from there) you can/should: > > - vacuum full > - reindex your tables > - reindex your indexes > Actually the manuals doesn't recomend VACUUM FULL for routine maintenance anymore """ For those not using autovacuum, one approach is to schedule a database-wide VACUUM once a day during low-usage period, supplemented by more frequent vacuuming of heavily-updated tables if necessary. (Some installations with extremely high update rates vacuum their busiest tables as often as once every few minutes.) If you have multiple databases in a cluster, don't forget to VACUUM each one; the program vacuumdb might be helpful. VACUUM FULL is recommended for cases where you know you have deleted the majority of rows in a table, so that the steady-state size of the table can be shrunk substantially with VACUUM FULL's more aggressive approach. Use plain VACUUM, not VACUUM FULL, for routine vacuuming for space recovery. """ -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I save data and then raise an exception?
On Thu, Oct 2, 2008 at 8:44 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Rob Richardson wrote: > >> Here's what I need to do: >> >> IF query_check_fails THEN >> UPDATE some_table SET some_value = 0 WHERE some_condition_is_true; >> RAISE EXCEPTION 'Look, you idiot, do it right next time!'; >> END; >> >> I need the update to work, but I need to raise the exception so the C++ >> code recognizes the error. How can I do both? > > You need an autonomous transaction, which Postgres does not support > directly but you can implement using dblink or a plperl function that > connects back to the database. > what about RAISE NOTICE? -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
On 10/15/08, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Vladimir Dzhuvinov wrote: > > > > That feature alone can help you enormously. Lest you think I'm > > > biased, I dba a mysql box professionally...every time I pop into the > > > mysql shell I feel like I'm stepping backwards in time about 5 years. > > > Don't let the inability to return multiple sets trip you up...you are > > > missing the big picture. > > > > Oh, I am not missing the big picture: Quit programming and take up the > > job of a lazy millionaire :) > > I don't quite understand you here. I'm sure we all crave the lazy > millionaire bit, but what would a lazy millionaire do other than > programming for fun? > read dozens of mails from a forum? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] filter statements in logs
Hi, A client has a web system that uses ADODB for php, and that driver is executing "select version()", "SET DATESTYLE TO 'ISO'" and at least one or two more statements a *lot* of times (almost 100 times in 3 hours, and this is just "select version()"), i tried to understand why but it seems it is for knowing the correct way of looking in catalogs. But this is just noise in the logs. And when i try to use pgFouine to analyze logs it shouts because of the size of them, almost 450Mb in a few hours. My question: is there a way to avoid logging some predefined statements? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] pg_get_serial_sequence Strangeness/Unreliable?
On Wed, Nov 26, 2008 at 3:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: >> Jeff MacDonald wrote: >>> Hi Tom, so far as I know the table "owns" the serial in so much as when i >>> do a \d of the table it says this > can we make \d show if the sequence is owned by the table (ie: serial or manually created and owned) or is a manually created and maked default sequence? maybe a flag? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] two postgres server seeing the same data
Hi, a client of mine is obsessed with the idea of having two postgres servers looking at the same partition of data... i think i can configure two servers pointing to the same $PGDATADIR and let one off and the other on but is it possible for load balancing, i mean with the two servers active? obviously one of the two should be only for reading... my thought is that it's not that beneficial because there will be the same set of disks (even being an storage) and i'm not sure is at all possible because the contents of shared memory in both servers will be different... thoughts? any one has tried that configuration? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] about privileges on pg_stat_activity columns
Hi, there is an auditor that want to monitor our database activity to see the session and the ip they come from, if they are waiting and so on... pg_stat_activity and pg_locks views come to my mind... we created an user to him and give him privileges to pg_locks and pg_stat_activity (and the functions pg_stat_activity is calling) but still he see the columns that comes from the functions as null... is there a way to give him access to that data without give him superuser? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] two postgres server seeing the same data
On Wed, Dec 3, 2008 at 1:10 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: > On Wed, Dec 03, 2008 at 09:43:24AM -0800, Joshua D. Drake wrote: >> You may have two servers pointed at $PGDATA but at no point can >> postgresql be running on both at the same time. > > More importantly, if you do this, you will probably be able to get the > two postmasters to start up. This will permanently corrupt the data. is this true even if one of the server just send SELECTs? > I know this partly because of experience with a "failover" system > whose interlocks failed. Two postmasters, one data area, and no > recoverable data. > the worst part of learning the lesson ;) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] two postgres server seeing the same data
On Wed, Dec 3, 2008 at 4:30 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Wed, Dec 3, 2008 at 2:29 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: >> On Wed, Dec 3, 2008 at 2:23 PM, Jaime Casanova >> <[EMAIL PROTECTED]> wrote: >>> On Wed, Dec 3, 2008 at 1:10 PM, Andrew Sullivan <[EMAIL PROTECTED]> wrote: >>>> On Wed, Dec 03, 2008 at 09:43:24AM -0800, Joshua D. Drake wrote: >>>>> You may have two servers pointed at $PGDATA but at no point can >>>>> postgresql be running on both at the same time. >>>> >>>> More importantly, if you do this, you will probably be able to get the >>>> two postmasters to start up. This will permanently corrupt the data. >>> >>> is this true even if one of the server just send SELECTs? >> >> yes. > > even if it sends NO queries. just starting the second postmaster is enough > ok... actually i see no real value in that config (create a single point of contention) but everyone that has used (or at least read about) oracle think is the best thing to do... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] about privileges on pg_stat_activity columns
On Thu, Dec 4, 2008 at 4:02 AM, Richard Huxton <[EMAIL PROTECTED]> wrote: > Jaime Casanova wrote: >> >> we created an user to him and give him privileges to pg_locks and >> pg_stat_activity (and the functions pg_stat_activity is calling) but >> still he see the columns that comes from the functions as null... is >> there a way to give him access to that data without give him >> superuser? > > I'd create a view or some functions with "security definer" privileges. > That way you can provide precisely the access needed. > exactly what i did... thanks to both -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] shared disk failover
Hi, any one has doing this... is there a good tutorial o directions for it? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] [HACKERS] New PostgreSQL Committers
On Mon, Dec 7, 2009 at 5:49 AM, Dave Page wrote: > > The new committers are: > > Robert Haas > Simon Riggs > Greg Stark > ITAGAKI Takahiro > > Congratulations! > +1 -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Implementing next 30 (or so) rows "sliding window"
On Tue, Dec 8, 2009 at 2:12 PM, Allan Kamau wrote:
> Hi,
> I did follow the basic advise and consulted the documentation for
> "SELECT" and came across "[ FETCH { FIRST | NEXT } [ count ] { ROW |
> ROWS } ONLY]" clause which seems to satisfy my requirement.
>
that's basically LIMIT, you have to combine that with OFFSET
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
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] Implementing next 30 (or so) rows "sliding window"
On Tue, Dec 8, 2009 at 3:51 PM, Scott Marlowe wrote:
> On Tue, Dec 8, 2009 at 12:12 PM, Allan Kamau wrote:
>> Hi,
>> I did follow the basic advise and consulted the documentation for
>> "SELECT" and came across "[ FETCH { FIRST | NEXT } [ count ] { ROW |
>> ROWS } ONLY]" clause which seems to satisfy my requirement.
>
> This is a cursor,
no. this is sql 2008 syntax for the LIMIT clause:
http://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-LIMIT
> which is one of two very basic ways of doing this.
i agree, that using cursor is another (preferred?) solution for this,
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
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] AccessShareLock question
On Sat, Dec 19, 2009 at 9:04 AM, Clayton Graf wrote: > I get an AccessShareLock in a simple select command and I am not using the > FOR SHARE clause. http://www.postgresql.org/docs/current/static/explicit-locking.html says: """ ACCESS SHARE Conflicts with the ACCESS EXCLUSIVE lock mode only. The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode. """ in other words, everything is ok, AccessShareLock doesn't block anything but with anyone trying to change the structure of the table (ALTER, DROP) and with commands TRUNCATE, REINDEX, CLUSTER, and VACUUM FULL, and every select take it -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] AccessShareLock question
On Sat, Dec 19, 2009 at 10:58 AM, Clayton Graf wrote: > Ok, but this is really my problem: I cannot perform an ALTER TABLE with the > system in production mode, because the ALTER TABLE hangs due an > AccessShareLock. until the lock is released, are your selects all that long? besides, why are you ALTERing the table in production... i guess clients will suffer if the expect less or more columns than the ones they receive from the ALTERed table > We use two-tier mode, don't understand this > so is it necessary to shutdown all users before > perform an ALTER TABLE? no -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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 table performance
On Sat, Dec 19, 2009 at 4:07 PM, Antonio Goméz Soto wrote: > Hi, > > is there a way in sql to dynamically test for version 7.3, so I can run the > are you planning to run this many times? what is wrong with making this manually? doesn't seem like something to make automatic... but if you insist in plpgsql you can execute "select version() into some_text_var" and act acordingly -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
2009/12/19 Andrus : > > set transaction isolation level read uncommitted; the "isolation level" is for specifying what rows are visible no for columns. besides, postgres doesn't implement "read uncommitted" > update test1 set a=4, b=a ; > > b value is 1 but must be 4. no. b value "must be" 1, you want it to be 4... in an update the columns always hold the old value until the statement is finished, the only way i can think for doing this is with a trigger -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] relation pg_autovacuum does not exist in postgresql 8.4.2
On Tue, Dec 22, 2009 at 1:18 PM, Antonio Sobalvarro wrote: > > This kind of programs get the error message “relation pg_autovacumm does not > exist”. > what programs are failing? anyway, the error is that those programs are accesing a catalog that doesn't exist anymore (actually i consider an error to access a catalog from an user application but...) if they are your programs and you have source code you can change them. if not, you can try to recreate the catalog for yourself but for do that you need: 1) stop the service and start in standalone mode with catalog changes allowed 2) create pg_catalog.pg_autovacuum with structure it has until 8.3 (i guess if those programs are broken enough to ask for the catalog maybe it is also asking for specific columns of the catalog) 3) Exit and start the service again -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
On Sat, Dec 19, 2009 at 7:16 PM, Christophe Pettus wrote: > >> I understand that it is not possible to read previous rows without >> creating hack using triggers. > > As noted above, that's not correct. You cannot access new values of a > particular row within a single UPDATE statement, but you do see new values > done in the same transaction. > what´s the problem with something as simple as: create function keep_a_in_b_test1() returns trigger as $$ begin new.b = old.a; return new; end; $$ language plpgsql; create trigger trg_keep_a_in_b_test1 before update on test1 for each row execute procedure keep_a_in_b_test1(); -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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
On Wed, Jan 13, 2010 at 11:31 AM, Amy Smith wrote: > all > how to set up PGTAB file ? any example for > PGTAB=/opt/postgres/utilities/conf/pgtab > what is pgtab for? and where do you get it? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Unique cosntraint based on contents of a field?
On Wed, Apr 7, 2010 at 10:30 PM, Warren Bell wrote: > Is there a way to create a unique constraint based on the content of a > field? For instance, say you have an integer field where you only want one > record with the number 1 in that field but there can be many records with > say the number 2 or any other single number in that field. > create unique index unique_field_idx on table1(field) where field = 1; -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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 Streaming Replication ==> Stop WAL Sender on master and WAL receiver on Slave
On Tue, Nov 1, 2011 at 1:35 PM, Samba wrote: > > But how do I disable streaming replication, with the effect that both WAL > sender on master and WAL receiver on slave gets stopped? Similarly, when I > enable streaming replication, the WAL sender and WAL receiver processes > would need to be started. > There is no such thing that i know. but the effect is the same as if you shutdown the standby. The problem is that if you have a standby server and stop walsender and walreceiver. what happens when you start them again: 1) there would be accumalated work (wal records to send and to receive), so it will take some time until standby catch up 2) what if the wal segment that was being used when you stopped walsender is reused in master before walsender is reactivated? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] synchronous replication + fsync=off?
On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg wrote: > Hello, > > I have two servers with battery backed power supply (USV). So it is > unlikely, that both will crash at the same time. > > Will synchronous replication work with fsync=off? > That means we will commit to system cache, but not to disk. Data will not > survive a system crash but the second system should still be consistent. > you should never use fsync=off (in production at least) the appropiate parameter to use is synchronous_commit which is the one that controls synchronous replication: off = no local nor remote synchronous commit local = local synchronous commit but no remote on = both, local and remote, synchronous commit synchronous commit = flushed to disk once all that said, i guess you can use fsync on any combination (off on master and on on standby, for your case) but i haven't tried. anyway that will guarantee you will lose your master instalation on OS crash and i think to remember that even if the OS doesn't crash there is a risk (altough i can't find the mail saying that) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] [ADMIN] Urgente error in restore prod
On Sun, Dec 14, 2008 at 10:39 AM, paulo matadr wrote: > Hi guys, i need urgent help with this error: > pg_restore: [archiver (db)] error returned by PQputCopyData: cannot allocate > memory for output buffer > im my restore . > any idea with solve this error? if you still have this error can you show what were you doing? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] monitor effectiveness of fillfactor and vacuums
Hi, currently i'm trying to $SUBJECT, my actual approach is to look at n_tup_upd and n_tup_hot_upd assuming the more near they are the better... is that a good assumption? what else can i see? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] explain
2009/1/7 Gustavo Rosso : > Es 1.213ms un valor correcto para realizar un insert en una tabla obtenido > por explain analyze. eso es un poco mas de un milisegundo, no parece mucho... en todo caso el tiempo que demore un INSERT dependera en parte de la cantidad de indices, FK y triggers que tenga una tabla -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] explain
On Wed, Jan 7, 2009 at 10:53 AM, hubert depesz lubaczewski wrote: > > p.s. as you can clearly see from another mails from this list - the list > uses english language. please use it, or if you'd like to use spanish > (i'm not sure if your language if spanish, i don't know it) - you can > use list pgsql-es-ayuda > (http://archives.postgresql.org/pgsql-es-ayuda/). > oops! i answered in spanish too! i never saw the list it was sent to... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Indexing columns with low cardinality: persistent bitmap indexes?
On Mon, Jan 12, 2009 at 4:16 PM, Reg Me Please wrote: > On Monday 12 January 2009 21:38:02 Bruno Lavoie wrote: >> Hello, >> >> I have a column with a small number of distinct values, indexing this >> one with a standard BTree is useless. How do I can index this column >> efficiently? I searched and it seems that pg doesn't support the >> creation of persistent bitmap indexes... Is that feature planned in next >> releases of pg? >> >> Thanks >> Bruno Lavoie > > I would try partial indexes, as many as the distinct values. > I'm not sure this would help, though. > you should create partial indexes only on those values that are a lower fraction on the table ie: if you have value "fraction of the table that has this value" 15% 23% 3 20% 4 25% 5 47% then only partial indexes on values 1 and 2 are of some value -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] Re: [GENERAL] Question regarding new windowing functions in 8.4devel
On Fri, Jan 16, 2009 at 12:07 PM, David Fetter wrote: >> >> Now i want only 3 records for every typ: >> >> test=# select typ, ts, rank() over (partition by typ order by ts desc ) >> from foo where rank <= 3; >> ERROR: column "rank" does not exist >> LINE 1: ...rtition by typ order by ts desc ) from foo where rank <= 3; > maybe the rank should go in a having clause? i'm not familiar about window functions yet... just guessing... > I tried this: > > SELECT >typ, >ts, >rank() over w AS foo_rank > FROM >foo >WINDOW w AS (partition by typ order by ts desc) > WHERE >foo_rank < 4; > > ERROR: syntax error at or near "WHERE" > LINE 8: WHERE >^ the WINDOW specification goes after the WHERE clause not before -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Rollback of Query Cancellation
On Wed, Jan 28, 2009 at 12:19 AM, Abdul Rahman wrote: > Dear All, > > PostgreSQL does not perform rollback action. Is it true? > no. postgresql executes all statements that are outside an explicit transaction in its own implicit one [1] executing commit at the end, if you cancelled the query the commit never executed and the records were never deleted... even more to the point, postgres never delete tuples on delete just mark them as invalid since transaction number X. at commit time the transaction is marked as correctly finished and all changes are WAL logged then suddenly changes take effect... rollback never mark the transaction as finished and doesn't save WAL records (that meaning that changes never spread to the datafiles) actually ROLLBACK is very cheap just don't save changes, COMMIT makes all the work... now, your post make me think that you think the ROLLBACK never executed based on... reponse time? very unscientific (there are plenty other reasons for that to happen) [1] http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Rollback of Query Cancellation
On Wed, Jan 28, 2009 at 12:56 AM, Abdul Rahman wrote: > > What do the logs show? > > Message just showed the time it took to delete certain number of records. > can you show that message? copy 'n pasted from logs!! -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Rollback of Query Cancellation
On Wed, Jan 28, 2009 at 1:29 AM, Abdul Rahman wrote: > Welcome Jcasanov, > > Here is the output of the log files: > > 2009-01-27 09:24:18 FATAL: the database system is starting up > 2009-01-27 09:24:19 LOG: database system was shut down at 2009-01-26 > 18:34:53 > 2009-01-27 09:24:19 LOG: checkpoint record is at 0/1B9F92C8 > 2009-01-27 09:24:19 LOG: redo record is at 0/1B9F92C8; undo record is at > 0/0; shutdown TRUE > 2009-01-27 09:24:19 LOG: next transaction ID: 0/335372; next OID: 19380 > 2009-01-27 09:24:19 LOG: next MultiXactId: 1; next MultiXactOffset: 0 > 2009-01-27 09:24:19 LOG: database system is ready all these messages are 'cause the database is starting up > 2009-01-27 18:52:43 LOG: received fast shutdown request > 2009-01-27 18:52:44 LOG: shutting down > 2009-01-27 18:52:44 LOG: database system is shut down > 2009-01-27 18:52:44 LOG: logger shutting down > the database was shutting down > and > > 2009-01-28 09:42:14 FATAL: the database system is starting up > 2009-01-28 09:42:14 LOG: database system was shut down at 2009-01-27 > 18:52:44 > 2009-01-28 09:42:14 LOG: checkpoint record is at 0/1B9F9368 > 2009-01-28 09:42:14 LOG: redo record is at 0/1B9F9368; undo record is at > 0/0; shutdown TRUE > 2009-01-28 09:42:14 LOG: next transaction ID: 0/336490; next OID: 19380 > 2009-01-28 09:42:14 LOG: next MultiXactId: 1; next MultiXactOffset: 0 > 2009-01-28 09:42:15 LOG: database system is ready > 2009-01-28 09:42:15 FATAL: the database system is starting up > starting up again none of these are saying the time for some records being deleted, read my prior post about why is not possible that message (commit never executed) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Rollback of Query Cancellation
On Wed, Jan 28, 2009 at 2:10 AM, Abdul Rahman wrote: > Thanks Jaime, > Plz consider the actual log files to explore the issue in detail. Because I > have pasted the log files of client machine, I am using (sorry). > > > 2009-01-27 18:29:25 STATEMENT: delete from ci_cin_type_v where > req_id='0824100207' > delete from ci_cust_type_v where req_id='0824100207' > delete from ci_dependents_v where req_id='0824100207' > delete from ci_employer_v where req_id='0824100207' > delete from ci_cor_sig_v where req_id='0824100207' > delete from ci_corporate_v where req_id='0824100207' > delete from ci_individual_v where req_id='0824100207' > delete from ci_cif_v where req_id='0824100207' then you have a lot of deletes, are there executing inside a transaction? are you calling a trigger? > 2009-01-27 18:29:41 ERROR: relation "ci_cust_type_v" does not exist this table does not exist > 2009-01-27 18:29:41 STATEMENT: delete from ci_cust_type_v where > req_id='0824100207' > 2009-01-27 18:52:08 LOG: could not receive data from client: No connection > could be made because the target machine actively refused it. > 2009-01-27 18:52:08 LOG: unexpected EOF on client connection > 2009-01-27 18:52:08 LOG: could not receive data from client: No connection > could be made because the target machine actively refused it. > 2009-01-27 18:52:08 LOG: unexpected EOF on client connection > guess this messages are received after the CANCEL QUERY if the series of deletes are all executed inside a transaction then they all were rollback if not only the last one (the one that generates the error) was rolledback -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] BUMP: Using foreign key constraint to eliminate unnecessary joins in view
On Mon, Apr 13, 2009 at 2:36 PM, Paul Wehr wrote: > > I am looking for a way to get the plan to show only a seq scan of test2, > since test1 does not contribute any columns, and we know from the > not-deferrable-not-null-primary-key that there will always be exactly one > match. > > Am I just missing something obvious, or does postgresql (currently) not do > that? > postgres will look at all tables involved, i think there is work to make it smart enough to make what you describe but that's in the future... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] ERROR: syntax error at or near "IF"... why?
On Thu, Apr 30, 2009 at 1:45 AM, DaNieL..! wrote: >> >> > IF (SELECT credit FROM users WHERE name = 'mary') < 0 THEN >> > ROLLBACK; >> > END IF >> > COMMIT; >> >> > i always get the error >> > ERROR: syntax error at or near "IF" >> if you're inside a server-side function then you cannot use COMMIT nor ROLLBACK; if you aren't inside a server-side function then you cannot use IF -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] WHERE
On 5/9/05, Hrishikesh Deshmukh <[EMAIL PROTECTED]> wrote: > Hi, > > I have two tables with genes and its annotation and a bunch of > parameters, i wanted to know what are the genes common to these two > tables, so i wrote this query > > create table temp as select gene from dataTable1 intersect > select gene from dataTable2; > > The resulting table has only genes list (one column). > > Now i want to retrieve gene annotation from datatable1, so how i guess > one has to write a query select geneAnnotation from dataTable1 where > genes= " temp table"; > select * from datatable1 where genes in (select gene from temp); Actually, i don't like the name "temp" for a table it seems error prone. -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Update on tables when the row doesn't change
On 5/24/05, Sebastian Böck <[EMAIL PROTECTED]> wrote: > Martijn van Oosterhout wrote: > > I'm sure I'm not the only one, but, what are you talking about? RULEs > > are not really obvious so it would help if you could post an example of > > what you mean... > > > > Have a nice day, > > Hi, I'm not really talking about rules. > > I'm talking about updates on *real* tables, and how to avoid > unnecessary updates on these tables if the row doesn't change. > > The situation looks like this: > > I have a view which is a join of a lot of tables. > > I have lot of conditional ON UPDATE rules to that view that split one > update to the view into updates on the underlying table. The condition > of each rule is constructed in a way that the underlying table only > gets an update if the corresponding values change. > > If I collapse all these rules into one conditional rule and pass all > the updates to the underlying tables, I get a lot of unnecessary > updates to these real tables, if the values don't change. > > Thats what I want to avoid. > > Sorry for not beeing that clear. > > Sebastian > > And how are you preventing the rule execute the update if the field has no change? That is way Martijn told you about showing the rule. AFAIK, if you execute an update on a view that has a ON UPDATE rule all the SQL ACTIONS specified for the rule will be perfomed INSTEAD OF the original update -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Trigger and arguments question
On 5/26/05, Hervé Inisan <[EMAIL PROTECTED]> wrote: > > Hi everybody! > > I have a trigger like this: > > CREATE TRIGGER mytrigger >AFTER INSERT OR UPDATE OR DELETE >ON myschema.mytable >FOR EACH ROW >EXECUTE PROCEDURE myschema.myfunction(myarg); > > It sends an argument to myfunction(), and I can retrieve this value in > TG_ARGV[0]. Fine. > What I'm trying to do is using TG_ARGV[0] to point to a field in NEW or OLD. > Is it possible? > > Something like NEW.TG_ARGV[0]... > > I'm trying to write a kind of generic function which I could use on multiple > tables with different field names (myarg being the field name). > But I can't get it to work. > > Any clues or other solutions? No. the argument of the trigger must be a string literal defined at creation time. maybe you better solution is simply a function -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] index row size 2728 exceeds btree maximum, 27
On 6/2/05, Richard Huxton wrote: > KÖPFERL Robert wrote: > > To me it seems that the definer of this table missed the concept index ... > > or the concept database > > One usually looks up data using a key, but if the whole row is the key, what > > data shall be looked up. > > > So short story long: Remove data from your index. The data column seems > > like the data to be looked up using the key > > (scan_id, host_ip, port_num, plugin_id, severity) or even less. > > Postgres is able to take several indices over distinct columns into account. > > Thus reducing the possible candidates to a hand full. > > So several indices are also an option > > Actually, Dinesh didn't mention he was using this for the speed of > lookup. He'd defined the columns as being the PRIMARY KEY, presumably > because he feels they are/should be unique. Given that they are rows > from a logfile, I'm not convinced this is the case. > If this a log he will need a timestamp field to be usefull, making that field part of the primary key and letting the data out of the primary has more sense to me. -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Issue with adding ORDER BY to EXCEPT.
> SELECT encounter.encounter_id, encounter_d.encounter_d_id > FROM encounter > JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id > EXCEPT > SELECT encounter.encounter_id, encounter_d.encounter_d_id > FROM encounter > JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id > JOIN p_l_d ON p_l_d.patient_id = encounter.patient_mpi > WHERE encounter_d.encounter_id = encounter.encounter_id > AND ((p_l_d.start_date <= encounter_d.from_date OR p_l_d.start_date IS > NULL) >AND (p_l_d.end_date >= encounter_d.from_date OR p_l_d.end_date IS NULL)) > ORDER BY encounter.encounter_id, encounter_d.encounter_d_id > > With the ORDER BY > NOTICE: adding missing FROM-clause entry for table "encounter" > NOTICE: adding missing FROM-clause entry for table "encounter_d" > ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the > result columns > I suppose this is because the columns in the except are the same that the ones in the main select and the order by get confused. i'm redirecting to hackers to know if this is a known bug or there is something wrong in the select? i don't see anything wrong!! -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] is this a bug ?
On 6/16/05, Cedric BRINER <[EMAIL PROTECTED]> wrote:
> hi,
>
> I'm facing this problem
>
> DROP TABLE test;
> DROP TABLE new_test;
> CREATE TABLE test (id serial NOT NULL UNIQUE, nom varchar(32));
> INSERT INTO test ("nom") values ('cedric');
> INSERT INTO test ("nom") values ('felix');
> INSERT INTO test ("nom") values ('julien');
>
> CREATE TABLE new_test (id serial NOT NULL UNIQUE, nom varchar(32));
> INSERT INTO new_test SELECT * FROM test;
>
Why create the table with a serial field if you're going to drop the
seq anyway just making it integer and this way you avoid your problem?
> ALTER TABLE new_test ALTER COLUMN id SET DEFAULT
> nextval('public.test_id_seq');
> DROP TABLE test;
>
> ALTER TABLE new_test RENAME TO test;
> ALTER TABLE new_test_id_key RENAME TO test_id_key;
>
> DROP SEQUENCE new_test_id_seq ;
> -- this give an error... is this normal
>
> is this the normal behaviour ?
>
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] default tablespaces
Hi all, is there a way to specify a default tablespace for tables and a different one for indexes without put it in the CREATE statement? i guess one way is to set 'default_tablespace' for table's tablespace create or table without any indexes and then set it for idx's tablespace and create all indexes, a workaround that come to my mind. Any other idea? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Transaction Handling in pl/pgsql
On 7/12/05, Craig Bryden <[EMAIL PROTECTED]> wrote: > Hi > > I am trying to get a better understanding of how transactions work in > pl/pgsql functions. I found the following text in the help: > "It is important not to confuse the use of BEGIN/END for grouping statements > in PL/pgSQL with the database commands for transaction control. PL/pgSQL's > BEGIN/END are only for grouping; they do not start or end a transaction" > but I am still a bit confused. > > Suppose I have a function that will be called from an application. Will all > the statements in the function be rolled back if the last one generates an > exception? or do I need to add code to a function to make that happen? > > suppose you have: select your_function(); your_function adds some rows but the last one gives an error, because all statements that are out of a transaction block are in its own transaction the select calling your_function is inside a transaction... so, the answer is yes... the statements inside the function will be rolled back -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] i need you help about postgresql(rollback)
On 7/13/05, Nee.Mem(倪明) <[EMAIL PROTECTED]> wrote: > systemguards，hi! > i see you wrote on this page > http://archives.postgresql.org/pgsql-general/2005-07/msg00319.php > > test exsample: >create or replace function test() >returns void as >' >begin >delete from regiondata; >rollback; >end; >'language 'plpgsql'; > but exception a error:CONTEXT: PL/pgSQL function "test" line 3 at SQL > statement > Use others' words : > >It is important not to confuse the use of BEGIN/END for grouping statements > > in PL/pgSQL with the database commands for transaction control. PL/pgSQL's > > BEGIN/END are only for grouping; they do not start or end a transaction > and can you tell me how to use rollback work in 'pgsql' function? and give > me a exsample? > As Alvaro told you can write ROLLBACK... if you are using pgsql 8.x.x then you can use exceptions... Alvaro gives you the examples... When a statement gives an error, it will go to the exception an all statements in the block will be rolled back. If you are using pgsql 7.x.x or lower... you will get an error an all your transaction will be rolled back -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] index row size exceeds btree maximum, 2713 - Solutions?
> I'm trying to load some data into PostgreSQL 8.0.3, and I got the error > message "index row size 2904 exceeds btree maximum, 2713". After a > bunch of searching, I believe that I am getting this error because a > value that I am indexing is longer than ~ 1/3 of the block size - or the > BLCKSZ variable in the src/include/pg_config_manual.h file. > > Am I correct so far? > > I need to fix this problem. I cannot change the indexed columns. > can you drop the index and retry? what typo is the column? -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
