Hi,
We are using postrges 9.0.3 cluster environment, with replication patch.
In a normal condition, if I restart current active postgres in standby
mode and current standby postgres in active mode, we are facing one
blocking issue.
On restarting current standby postgres in active mode, the
Hey Melvin,
2011/2/22 Melvin Davidson melvin6...@yahoo.com
Other than It's currently not available, can anyone provide a logical
explanation of why triggers cannot be implemented for SELECT statements, or
rules for SELECT must be DO INSTEAD SELECT?
PostgreSQL was derived from Ingres, and
Dear all,
Today I need to back up a mysql database and restore in Postgresql
database but I don't know how to achieve this accurately.
Can anyone kindly describe me the way to do this.
Thanks best Regards,
Adarsh Sharma
--
Sent via pgsql-general mailing list
I have two table:
CREATE TABLE roles(role_name varchar(255) primary key);
CREATE TABLE roles_permissions(permission varchar(100), role_name varchar(100));
here is result by :
SELECT * from roles;
role_name
---
role1
role2
(2 rows)
here is result by :
SELECT * from
Hi,
Is any solution (I mean in code and internal based), any API. That
allows to create system trigger or handle on table. I'm interested in
tracking changes and coercing values on row change/insert/remove - user
may not to disable such trigger. In addition It is possible to track
changes to
On Tuesday 22 February 2011 10:21:01 Adarsh Sharma wrote:
Hi,
Today I need to back up a mysql database and restore in Postgresql
database but I don't know how to achieve this accurately.
Can anyone kindly describe me the way to do this.
Have a look here:
Greg Sabino Mullane wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
The saga continues. I've reverted to a multi-step process to try and
figure this out. I create the initial database, then load it from the
command line psql as follows:
pro-# \set session_replication_role
Dmitriy
Why not use function which returns table and wrap the
logging (auditing) code in it ?
Because to use a trigger function, you need a trigger, and as previously
stated, you cannot have a trigger on select. The same applies for a rule.
Melvin Davidson
2011/2/22 Melvin Davidson melvin6...@yahoo.com
Dmitriy
Why not use function which returns table and wrap the
logging (auditing) code in it ?
Because to use a trigger function, you need a trigger, and as previously
stated, you cannot have a trigger on select. The same applies for a rule.
Melvin: The proposal is to do something of the form
SELECT * FROM selecting_function()
And have selecting_function() perform any necessary auditing.
I guess this work fairly well - as long as you remember to remove SELECT
privileges on the wrapped table from everyone and setup
Take a look at the Navicat for PostgreSQL.
--
Thanks Regards
Dhaval Jaiswal
Cell: +91 80953.978.43
From: pgsql-general-ow...@postgresql.org on behalf of Adarsh Sharma
Sent: Tue 2/22/2011 2:51 PM
To: pgsql-general@postgresql.org
Cc: my...@lists.mysql.com
Dave and Dmitriy,
I know a function can be used, but the point is to log a table whenever
someone else does a SELECT on it.
It cannot be depended on that a user will include that (or any specific
function in a SELECT. iow, when any user does SELECT ... FROM tablex; then
logging should occur.
Melvin Davidson, 22.02.2011 15:42:
I know a function can be used, but the point is to log a table
whenever someone else does a SELECT on it.
It cannot be depended on that a user will include that (or any
specific function in a SELECT. iow, when any user does SELECT ...
FROM tablex; then logging
ivan_14_32 ivan_14...@mail.ru writes:
Q: How can I send tuple id (integer primary key) via notify using Rule?
You can't --- NOTIFY doesn't take variables in its parameters.
(That's a general property of utility commands, not just NOTIFY.)
Use the pg_notify() function, instead.
What am I missing that causes this to resort to sorting on disk?
obc=# select version();
version
--
PostgreSQL 8.3.7 on
zab08 za...@126.com wrote:
I have two table:
CREATE TABLE roles(role_name varchar(255) primary key);
CREATE TABLE roles_permissions(permission varchar(100), role_name varchar
(100));
here is result by :
SELECT * from roles;
role_name
---
role1
role2
(2 rows)
It's come time to bite the bullet and convert a half-terraybyte database from
ASCII to UTF8. Have gone through a bit of effort to track down the unclean
ascii text and repair it but would like to avoid the outage of a many-many hour
dump-restore.
Using Postgres 8.4.X.
Are there any other
Reid Thompson reid.thomp...@ateb.com wrote:
What am I missing that causes this to resort to sorting on disk?
obc=# select version();
version
Hi,
a puzzle to solve...
I have a table with a primary key, and a timestamp, e.g.
idstamp
1 2011-02-01 10:00
2 2011-02-01 09:00
3 2011-02-01 11:00
Now for reasons too painful to go into, I need to reorder the id
(sequence) so that they are in time order:
idstamp
1
Reid Thompson reid.thomp...@ateb.com writes:
What am I missing that causes this to resort to sorting on disk?
The in-memory space required to sort N tuples can be significantly
larger than the on-disk space, because the latter representation is
optimized to be small and the in-memory
On Feb 22, 2011, at 10:23 PM, Jeff Amiel wrote:
It's come time to bite the bullet and convert a half-terraybyte database from
ASCII to UTF8. Have gone through a bit of effort to track down the unclean
ascii text and repair it but would like to avoid the outage of a many-many
hour
On Tue, Feb 22, 2011 at 11:53 AM, Jeff Amiel becauseimj...@yahoo.com wrote:
I assume slony replication is an option.
this is my plan, once i finish cleaning up the code and the DB data.
you have to ensure that whatever the original DB emits (in the form of
COPY and individual updates later
On Tue, Feb 22, 2011 at 04:40:36PM +, Howard Cole wrote:
- Hi,
-
- a puzzle to solve...
-
- I have a table with a primary key, and a timestamp, e.g.
-
- idstamp
- 1 2011-02-01 10:00
- 2 2011-02-01 09:00
- 3 2011-02-01 11:00
-
- Now for reasons too painful to go into, I need
Tom Lane t...@sss.pgh.pa.us wrote:
Reid Thompson reid.thomp...@ateb.com writes:
What am I missing that causes this to resort to sorting on disk?
The in-memory space required to sort N tuples can be significantly
larger than the on-disk space, because the latter representation is
optimized
On Feb 22, 2011, at 10:10 PM, Howard Cole wrote:
Hi,
a puzzle to solve...
I have a table with a primary key, and a timestamp, e.g.
idstamp
1 2011-02-01 10:00
2 2011-02-01 09:00
3 2011-02-01 11:00
Now for reasons too painful to go into, I need to reorder the id
On 02/22/2011 12:06 PM, Tom Lane wrote:
Reid Thompson reid.thomp...@ateb.com writes:
What am I missing that causes this to resort to sorting on disk?
The in-memory space required to sort N tuples can be significantly
larger than the on-disk space, because the latter representation is
Andreas Kretschmer akretsch...@spamfence.net writes:
Tom Lane t...@sss.pgh.pa.us wrote:
Reid Thompson reid.thomp...@ateb.com writes:
What am I missing that causes this to resort to sorting on disk?
The in-memory space required to sort N tuples can be significantly
larger than the on-disk
On Tue, Feb 22, 2011 at 1:03 AM, ivan_14_32 ivan_14...@mail.ru wrote:
Hi!
I trying to create rule for update notifing:
CREATE OR REPLACE RULE ttt_NotifyU AS
ON UPDATE TO ttt DO
NOTIFY ttt, '88';
this work.
But this
CREATE OR REPLACE RULE ttt_NotifyU AS
ON UPDATE TO ttt DO
On Tue, Feb 22, 2011 at 1:03 AM, ivan_14_32 ivan_14...@mail.ru wrote:
Hi!
I trying to create rule for update notifing:
CREATE OR REPLACE RULE ttt_NotifyU AS
ON UPDATE TO ttt DO
NOTIFY ttt, '88';
this work.
But this
CREATE OR REPLACE RULE ttt_NotifyU AS
ON UPDATE TO ttt DO
Something like:
SELECT
ordered.stamp,
nextval('sequence') AS rownumber
FROM (SELECT stamp FROM table ORDER BY stamp ASC) ordered
Incorporate the ID field and UPDATE as necessary to get the result the way
you need it.
You are apparently aware that you likely have a design or understanding
On 22/02/2011 5:18 PM, Vibhor Kumar wrote:
If you are using PG 8.4 then you can try something with row_number as
given below:
select id,stamp, row_number() over(order by stamp) from test;
Or
Create table test1 as select row_number() over(order by stamp) as id, stamp
from test;
Thanks
Hi,
I'm passing a tablename and two columnnames into a function so that I
can SELECT Records in a FOR LOOP using 'fixed' field names.
Using the passed params I construct the create table command and then
alter the names of two columns.
When I 'build' the function and then run my query to use
A little lost but the first thing that stands out is that you are attempting
to create an actual table instead of a temporary table. Not sure if that
difference is meaningful to the function but procedurally is there a reason
to create the permanent table instead of a temporary one?
If you do
2011/2/22 Ralph Smith rsm...@10kinfo.com:
Hi,
I'm passing a tablename and two columnnames into a function so that I can
SELECT Records in a FOR LOOP using 'fixed' field names.
Using the passed params I construct the create table command and then alter
the names of two columns.
When I
Here's what I'm doing.
It is to work on existing tables (not triggerable), but for subsequent
updates to the table(s) that I'm tokenizing fields for, a trigger will
be used to do the tokenizing of new data
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _
This worked!!!
FOR TableRec IN EXECUTE ExecuteString LOOP
THANKS ALL!!!
Ralph
p.s. The reason we're still using 7.4 is that some system logs were
trashed and we NEED that data. All but the BLOGS have been recovered,
and there lies the problem.
=
Pavel
-Original Message-
From: Melvin Davidson [mailto:melvin6...@yahoo.com]
Sent: Tuesday, February 22, 2011 9:43 AM
To: 'Dmitriy Igrishin'; David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: why is there no TRIGGER ON SELECT ?
Dave and Dmitriy,
I know a function can be
Hi. Last week our 60 GB database (per psql \l+) was (re-)replicated to
the DR site using SlonyI, and arrived 109 GB in size which caused a
problem as it filled up the filesystem on the DR server - we expected the
DR database to be the same size. Mystery.
Now just past weekend we upgraded our
Kevin Grittner kevin.gritt...@wicourts.gov writes:
PL/pgSQL seems tantalizingly close to being useful for developing a
generalized trigger function for notifying the client of changes. I
don't know whether I'm missing something or whether we're missing a
potentially useful feature here.
Hello,
Per the customary URL:
https://www.postgresqlconference.org/
JD
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc |
[moving to -hackers with BC to -general]
Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
PL/pgSQL seems tantalizingly close to being useful for developing
a generalized trigger function for notifying the client of
changes. I don't know
I don't know if you can quite write the generalized notification function you
want in plpgsql or not, but you can certainly write the meta-function that
create the function for any table ;-)
--
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
--
V8.3.4 on linux
How does one do a regexp match/test in PlPgsql given a text variable containing
a regexp and another text variable containt the string to test. Example that
shows what I'm trying to do...
declare
rgxp text;
str1 text;
str2 text;
begin
rgxp := '[a-zA-Z0-9]';
str1 :=
On 23 February 2011 11:55, Gauthier, Dave dave.gauth...@intel.com wrote:
I would expect to see...
ShouldBeOK99 is a match
Should_Fail_match77 is not a match
Why would you expect that? Both strings match at least one
character from the character class?
Cheers,
Andrej
--
Sent via
You are trying to check the entire string to ensure only the specified
character class matches at each position. What you are doing is seeing
whether or not there is at least one character class matching value in the
tested string.
Since you want to check the entire string you should:
Anchor
I'm sure many of you have solved this problem in the past and can offer
solutions that will work for me. The context is a 73-column postgres table
of data that was originally in an Access .mdb file. A colleague loaded the
file into Access and wrote a .csv file for me to use since we have
On 02/22/2011 07:10 PM, Rich Shepard wrote:
I'm sure many of you have solved this problem in the past and can offer
solutions that will work for me. The context is a 73-column postgres table
of data that was originally in an Access .mdb file. A colleague loaded the
file into Access and wrote a
On 02/22/2011 07:25 PM, Andy Colson wrote:
On 02/22/2011 07:10 PM, Rich Shepard wrote:
I'm sure many of you have solved this problem in the past and can offer
solutions that will work for me. The context is a 73-column postgres table
of data that was originally in an Access .mdb file. A
thanks , this is a example of my application.
the ans is here, http://www.postgresql.org/docs/9.0/static/xaggr.html
sql command:
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
SELECT r.role_name, array_accum(permission)
On Tuesday, February 22, 2011 5:10:34 pm Rich Shepard wrote:
I'm sure many of you have solved this problem in the past and can offer
solutions that will work for me. The context is a 73-column postgres table
of data that was originally in an Access .mdb file. A colleague loaded the
file
On Tue, 22 Feb 2011, Adrian Klaver wrote:
We are going to need to see at least a sample of the actual data in
one.csv that is causing the problem. You have an off by two error as you
suggest, but that could actually have happened earlier in the row. For
instance the well_finish_date would
2011/2/22 Adarsh Sharma adarsh.sha...@orkash.com:
Dear all,
Today I need to back up a mysql database and restore in Postgresql database
but I don't know how to achieve this accurately.
Have a look at: mysqldump --compatible=postgresql command:
On 02/22/11 1:25 AM, Jaime Crespo Rincón wrote:
2011/2/22 Adarsh Sharmaadarsh.sha...@orkash.com:
Dear all,
Today I need to back up a mysql database and restore in Postgresql database
but I don't know how to achieve this accurately.
Have a look at: mysqldump --compatible=postgresql command:
Please take a look at this article:
http://securfox.wordpress.com/2010/12/12/converting-mysql-to-postgresql/
I think also, that there are a tool that can do this easly,
Regards,
On Tue, Feb 22, 2011 at 9:21 AM, Adarsh Sharma adarsh.sha...@orkash.comwrote:
Dear all,
Today I need to back up a
Rich,
The data and table structure provided do not seem to correlate.
Regardless, if you changed the delimiter to | from , it is possible that
you converted an embedded , in one of the textual fields into a | when
you should not have.
For Instance:
Value1,value2,value, with comma,value4
Thus
Dear all,
I performed backup of databases in Database Servers but Today when I'm
going to backup of one of databases , the following error occurs :
[root@s8-mysd-2 8.4SS]# bin/pg_dump -Upostgres -i -o pdc_uima_olap |
gzip -c /hdd4-1/pdc_uima_olap108feb18.sql.gz
Password:
pg_dump: [archiver
Adarsh Sharma adarsh.sha...@orkash.com writes:
Dear all,
I performed backup of databases in Database Servers but Today when I'm
going to backup of one of databases , the following error occurs :
[root@s8-mysd-2 8.4SS]# bin/pg_dump -Upostgres -i -o pdc_uima_olap |
gzip -c
Hi All,
I am using System DSN, that connects to postgreSQL, to fetch data from the
database, and put into xls sheet .Its working fine with most of the machines
and connects fine but on 1 machine i am getting this FATAL: no pg_hba.conf
entry for host “::1**”, user “postgres”, database
On 02/22/11 10:38 PM, itishree sukla wrote:
Hi All,
I am using System DSN, that connects to postgreSQL, to fetch data
from the database, and put into xls sheet .Its working fine with most
of the machines and connects fine but on 1 machine i am getting this
FATAL: no pg_hba.conf entry for
Hello
If I have partitioned a table based on a foreign key in a manner where every
child table will only have data for single value of the foreign key, do I
need to create a index for the foreign key in the primary and/or child
tables? I am using version 8.4
With regards
Amitabh
60 matches
Mail list logo