Re: [GENERAL] What could keep a connection / query alive?
Derrick Rice writes: > I'm investigating (using 8.2) an instance of a database client > connection remaining open in a single query well past statement > timeout settings.? I understand that severed TCP connections can cause > the backend to hang until the connection is closed, but our tcp > keepalive settings should have recognized that condition after about > 30 minutes.? The connection and backend didn't terminate for nearly 90 > minutes. > > What can cause this?? Why would these tcp and statement timeout settings not > terminate the backend? Try trussing the backend process. You may find it in a network IO wait trying to send data to a client that is hung or over a socket that was timed out by a firewall or network equipment. Such a condition will cause the backend to be unable to hear the cancel. The statement will still show as running in pg_stat_activity. SIGTERM on such a backend will probably also fall on deaf ears. This has been my experience several times in an environment of EnterpriseDB 8.2 systems on Solaris 10. YMMV > -- Jerry Sievers e: gsiever...@comcast.net p: 305.321.1144 -- 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] Autocommit off - commits/rollbacks
Craig Ringer writes: > On 03/14/2011 10:55 PM, Vogt, Michael wrote: > >> Hey all >> >> I have a question, using the autocommit off option in postgres. >> >> As starting position I use a table called xxx.configuration using a >> unique id constraint. >> >> Why does postgres rollback the whole transaction after an error? > > It's a PostgreSQL limitation (or, arguably, optimization). When a Well, any transactional RDBMS whatsoever should behave that way. Hardly a PostgreSQL exclusive feature :-) -- Jerry Sievers e: gsiever...@comcast.net p: 305.321.1144 -- 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] Autocommit off - commits/rollbacks
Yes We can use exception for each statement to restrict the rollback. But how we can use SAVEPOINT and rollback to SAVEPOINT in the stored function or procedure in POSTGRES? We can only use the savepoints in transactions but not in stored functions. Regards, Tushar On Tue, Mar 29, 2011 at 12:54 PM, Jerry Sievers wrote: > Craig Ringer writes: > > > On 03/14/2011 10:55 PM, Vogt, Michael wrote: > > > >> Hey all > >> > >> I have a question, using the autocommit off option in postgres. > >> > >> As starting position I use a table called xxx.configuration using a > >> unique id constraint. > >> > >> Why does postgres rollback the whole transaction after an error? > > > > It's a PostgreSQL limitation (or, arguably, optimization). When a > > Well, any transactional RDBMS whatsoever should behave that way. Hardly > a PostgreSQL exclusive feature :-) > > -- > Jerry Sievers > e: gsiever...@comcast.net > p: 305.321.1144 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] anonymous record as an in parameter
Hi there, i'd like to write a function (sql or plpgsql) that takes an anonymous record as an in parameter. You know, kind of like (simplified): create function f_tablename (p_anyrecord record) returns text as $body$ select $1.tableoid::regclass::text $body$ language sql; ...but PG 9.0.3 doesn't like that: ERROR: SQL functions cannot have arguments of type record. It also doesn't work with plpgsql. Is there a way? Best wishes from Berlin, Maximilian Tyrtania Maximilian Tyrtania Software-Entwicklung 10969 Berlin http://www.contactking.de -- 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 9 silent installation on Windows
Thank you vibhork. I am using one-click installer by EnterpriseDB. It is successfully installed silently from command prompt. But I need to install silently from my .Net application. I use shell command to call. When running my application, a dialog box opened, there is a list of currently opening windows with Cancel , Retry, Ignore buttons. I want to ignore this dialog box from my application without user interaction. How could I do? Kindly explain. On Mon, Mar 28, 2011 at 7:54 PM, Vibhor Kumar wrote: > > On Mar 28, 2011, at 6:49 PM, Kalai R wrote: > > > I need to install postgres 9 silently on Windows. > > Kindly give the parameters list for postgres 9.0.3 > > > If you are using Source code to install PG9, then you can: > 1. Download the source code > 2. execute > ./configure > make > make install > > If you are using one-click installer by EnterpriseDB then you can use > "unattended mode" of installer. Following is a link: > > http://www.enterprisedb.com/docs/en/8.4/instguide/Postgres_Plus_Advanced_Server_Installation_Guide-15.htm#P889_74430 > > Thanks & Regards, > Vibhor Kumar > EnterpriseDB Corporation > The Enterprise PostgreSQL Company > vibhor.ku...@enterprisedb.com > Blog:http://vibhork.blogspot.com > >
[GENERAL] postgresql-9.0 service starting problem
Hi, I am using Windows XP. When I have installed PostgreSQL 9.0.3, the service didn't start automatically. In the "Computer Management" I explicitly start "postgresql-9.0" service, the service didn't start and following message displayed "The postgresql-9.0 service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service" What is the problem and How to solve it? Thanks and Regards kalai
Re: [GENERAL] Autocommit off - commits/rollbacks
On Tuesday, March 29, 2011 4:35:04 am tushar nehete wrote: > Yes We can use exception for each statement to restrict the rollback. > But how we can use SAVEPOINT and rollback to SAVEPOINT > in the stored function or procedure in POSTGRES? > We can only use the savepoints in transactions but not in stored functions. > > Regards, > Tushar > See below for how to achieve the same effect using EXCEPTIONS in pl/pgsql: http://www.postgresql.org/docs/9.0/interactive/plpgsql-control- structures.html#PLPGSQL-ERROR-TRAPPING -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] anonymous record as an in parameter
On Tue, Mar 29, 2011 at 8:07 AM, Maximilian Tyrtania wrote: > Hi there, > > i'd like to write a function (sql or plpgsql) that takes an anonymous record > as an in parameter. You know, kind of like (simplified): > > create function f_tablename (p_anyrecord record) returns text as > $body$ > select $1.tableoid::regclass::text > $body$ > language sql; > > ...but PG 9.0.3 doesn't like that: > ERROR: SQL functions cannot have arguments of type record. It also doesn't > work with plpgsql. > Is there a way? yes, in C, which is maybe 10 notches up in the difficulty level. my advice is to cast the record to text, or use an hstore. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Load Increase
PostgreSQL 9.0.3 has been running very smooth for us and we have streaming replication running on it as well as WAL archiving. Things have run consistently and we are extremely happy with the performance. During the early morning hours, we have processes that run and import certain data from clients, nothing too crazy: about 4-5 Mb CSV files being imported in. This runs flawlessly, however, this morning the load of the servers were high and a few of the input processes were running for over 2 hours. The load was around 4.00 and stayed there for a while. The import scripts eventually finished and the load went back down, however, any time there was a heavy write, the load would spike. I don't know whether this is because traffic on the database box increased or whether it was Postgres/Kernel related. I saw this in my dmesg: Things appear to be normal but I want to ask: what is a heavy load just by looking at uptime and also what causes the load to increase under reasonably heavy writes? Is it the streaming that could be causing some load increase? Thank you Ogden [3215764.704206] INFO: task postmaster:5087 blocked for more than 120 seconds. [3215764.704236] "echo 0 > /proc/sys/kernel/hung_task_timeout_secs" disables this message. [3215764.704281] postmasterD 0 5087 20996 0x [3215764.704285] 88043e46b880 0086 [3215764.704289] 8800144ffe48 8800144ffe48 f9e0 8800144fffd8 [3215764.704293] 00015780 00015780 88043b50d4c0 88043b50d7b8 [3215764.704296] Call Trace: [3215764.704302] [] ? __mutex_lock_common+0x122/0x192 [3215764.704306] [] ? getname+0x23/0x1a0 [3215764.704309] [] ? mutex_lock+0x1a/0x31 [3215764.704314] [] ? virt_to_head_page+0x9/0x2a [3215764.704318] [] ? generic_file_llseek+0x22/0x53 [3215764.704322] [] ? sys_lseek+0x44/0x64 [3215764.704325] [] ? system_call_fastpath+0x16/0x1b [3215764.704328] INFO: task postmaster:5090 blocked for more than 120 seconds. [3215764.704357] "echo 0 > /proc/sys/kernel/hung_task_timeout_secs" disables this message. [3215764.704402] postmasterD 0 5090 20996 0x [3215764.704406] 88043e46b880 0082 [3215764.704410] 88001433de48 88001433de48 f9e0 88001433dfd8 [3215764.704414] 00015780 00015780 88043b7569f0 88043b756ce8 [3215764.704418] Call Trace: [3215764.704421] [] ? __mutex_lock_common+0x122/0x192 [3215764.704425] [] ? getname+0x23/0x1a0 [3215764.704428] [] ? mutex_lock+0x1a/0x31 [3215764.704431] [] ? virt_to_head_page+0x9/0x2a [3215764.704435] [] ? generic_file_llseek+0x22/0x53 [3215764.704438] [] ? sys_lseek+0x44/0x64 [3215764.704441] [] ? system_call_fastpath+0x16/0x1b -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] not like perl..
I have a simple table with a varchar(32) field that I am trying to extract data using regular expressions. select * from spam where inetaddr like '100.%' row | inetaddr | tdate --+--+--- 3245 | 100.81.98.51 | 03/08/2011 07:21:19.29209-works fine select * from spam where inetaddr like E'\d\d\d.%' row | inetaddr | tdate -+--+--- (0 rows) --- zip ??? slect * from spam where inetaddr like E'\d.%' row |inetaddr| tdate ---++ 49424 | d.russell...@jdmarketing.co.uk | 03/27/2011 15:46:41.110566 ??? though \d was a digit match select * from spam where inetaddr like E'\\d.%' row |inetaddr| tdate ---++ 49424 | d.russell...@jdmarketing.co.uk | 03/27/2011 15:46:41.110566 ??? What am I doing wrong??? thanks h...@lota.us -- 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-9.0 service starting problem
On 29/03/2011 14:59, Kalai R wrote: Hi, I am using Windows XP. When I have installed PostgreSQL 9.0.3, the service didn't start automatically. In the "Computer Management" I explicitly start "postgresql-9.0" service, the service didn't start and following message displayed "The postgresql-9.0 service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service" What is the problem and How to solve it? Sounds like there was a problem look in the Postgres log, in the Windows event log, or both. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] not like perl..
On Tue, Mar 29, 2011 at 10:18 AM, hook wrote: > What am I doing wrong??? > You are confusing regular expressions with SQL "LIKE". Ie, you are using "LIKE" and expecting it to match some odd notion of regexp. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is pglesslog stable for use in 9.0?
I'm interested in using the pglesslog tools, pg_compresslog and pg_decompresslog, to reduce the size of WAL logs for a 9.0 setup I'm working with. I see that 1.4.2beta was released with 9.0 support, but that was posted over 9 months ago with no word on a non-beta version (per http://pgfoundry.org/projects/pglesslog). I also attempted to see whether there was any source activity but the CVS source tree appears to be empty. So is anyone currently using this in production with 9.0.x? Does anyone know the status of the project itself? Any info would be appreciated as the project looks very appealing otherwise. thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Date conversion using day of week
I'm trying to validate a day of the week, and thought that to_date would
do the job for me. But I found a case where it cannot tell the
difference between sunday and monday. Is this a bug or intended
behaviour?
dev=# select to_date('2011-13-Mon', '-IW-DY');
to_date
2011-03-28
(1 row)
dev=# select to_date('2011-13-Sun', '-IW-DY');
to_date
2011-03-28
(1 row)
dev=# select to_date('2011-13-Tue', '-IW-DY');
to_date
2011-03-29
(1 row)
This is on postgres 8.3.14.
__
Marc
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] not like perl..
On Mar 29, 2011, at 10:18 AM, hook wrote:
> I have a simple table with a varchar(32) field that I am trying to extract
> data using regular expressions.
>
> select * from spam where inetaddr like '100.%'
> row | inetaddr | tdate
> --+--+---
> 3245 | 100.81.98.51 | 03/08/2011 07:21:19.29209-works fine
>
>
> select * from spam where inetaddr like E'\d\d\d.%'
> row | inetaddr | tdate
> -+--+---
> (0 rows)
> --- zip ???
>
> slect * from spam where inetaddr like E'\d.%'
> row |inetaddr| tdate
> ---++
> 49424 | d.russell...@jdmarketing.co.uk | 03/27/2011 15:46:41.110566
> ??? though \d was a digit match
>
>
> select * from spam where inetaddr like E'\\d.%'
> row |inetaddr| tdate
> ---++
> 49424 | d.russell...@jdmarketing.co.uk | 03/27/2011 15:46:41.110566
> ???
>
>
> What am I doing wrong???
You are not using the regular expression operator.
test=# create table test(a text);
CREATE TABLE
test=# insert into test(a) values ('100.81.98.51');
INSERT 0 1
test=# select * from test where a ~ $$^\d+\.$$;
a
--
100.81.98.51
(1 row)
This is just like perl.
Cheers,
M
--
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] Date conversion using day of week
On Tuesday, March 29, 2011 8:07:48 am Marc Munro wrote:
> I'm trying to validate a day of the week, and thought that to_date would
> do the job for me. But I found a case where it cannot tell the
> difference between sunday and monday. Is this a bug or intended
> behaviour?
>
> dev=# select to_date('2011-13-Mon', '-IW-DY');
> to_date
>
> 2011-03-28
> (1 row)
>
> dev=# select to_date('2011-13-Sun', '-IW-DY');
> to_date
>
> 2011-03-28
> (1 row)
>
> dev=# select to_date('2011-13-Tue', '-IW-DY');
> to_date
>
> 2011-03-29
> (1 row)
>
> This is on postgres 8.3.14.
>
> __
> Marc
Well in 9.0.3 this raises an error:
select to_date('2011-13-Sun', '-IW-DY');
ERROR: invalid combination of date conventions
HINT: Do not mix Gregorian and ISO week date conventions in a formatting
template
From the docs:
http://www.postgresql.org/docs/9.0/interactive/functions-formatting.html
"An ISO week date (as distinct from a Gregorian date) can be specified to
to_timestamp and to_date in one of two ways:
Year, week, and weekday: for example to_date('2006-42-4', 'IYYY-IW-ID') returns
the date 2006-10-19. If you omit the weekday it is assumed to be 1 (Monday).
Year and day of year: for example to_date('2006-291', 'IYYY-IDDD') also returns
2006-10-19.
Attempting to construct a date using a mixture of ISO week and Gregorian date
fields is nonsensical, and will cause an error. In the context of an ISO year,
the concept of a "month" or "day of month" has no meaning. In the context of a
Gregorian year, the ISO week has no meaning. Users should avoid mixing
Gregorian
and ISO date specifications. "
So try this:
Monday
select to_date('2011-13-1', 'IYYY-IW-ID');
to_date
2011-03-28
Sunday
select to_date('2011-13-7', 'IYYY-IW-ID');
to_date
2011-04-03
--
Adrian Klaver
adrian.kla...@gmail.com
Re: [GENERAL] Date conversion using day of week
On 03/29/2011 08:07 AM, Marc Munro wrote:
I'm trying to validate a day of the week, and thought that to_date would
do the job for me. But I found a case where it cannot tell the
difference between sunday and monday. Is this a bug or intended
behaviour?
dev=# select to_date('2011-13-Mon', '-IW-DY');
to_date
2011-03-28
Based on running the queries in 9.0, it's behavior that has been corrected:
select to_date('Mon1-13-Tue', '-IW-DY');
ERROR: invalid combination of date conventions
HINT: Do not mix Gregorian and ISO week date conventions in a
formatting template.
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Script perl para eliminar constraints duplicados
Hi,
Sometimes i end up with some duplicated constraints definitions in my database,
i've noticed this when i reverse engineer databases, and see many links between
two tables.
I prepared a perl script that read an schema on standard input, and prints on
standard output some drop constraints for duplicated definitions, if you like
you can then execute them against your database:
For example if you save it as drop-dup-constraints.pl, then you can check your
database with:
pg_dump -Ox -s mydatabase | drop-dup-constraints.pl
#!/usr/bin/perl
use strict;
use warnings;
## Elimina los constraints sobre el mismo campo y la misma tabla
my ($table, $constName, $field);
my %tuplas;
my @lines = ();
while (<>) {
next if /--/;
chomp;
push @lines, $_;
# Ensamblar el sql acumulado e imprimir
if (/;/) {
&processLine(join " ", @lines);
@lines = ();
}
}
# Results
TUPLA: while (my ($k,$v) = each %tuplas) {
next TUPLA if @$v == 1;
# print "$k\n";
my @arr = @$v;
shift @arr; # let the first constraint
my ($table) = split /,/, $k;
foreach my $constraint (@arr) {
# print "\t$_\n";
printf "alter table %s drop constraint %s;\n", $table, $constraint;
}
}
exit 0;
my $lastSchema = "public";
sub processLine {
local $_ = shift;
chomp;
$lastSchema = $1 if /SET search_path = (\w+)/;
if (my ($table, $constName, $constraintType, $constraintName) = /ALTER TABLE
ONLY (\w+)\s+ADD CONSTRAINT (\w+) (FOREIGN KEY|UNIQUE)\s*\((\w+)\)/) {
my $key = "$lastSchema.$table,$constraintType.$constraintName";
my $aref = $tuplas{$key};
unless ($aref) {
$aref = [];
$tuplas{$key} = $aref;
}
push @$aref, $constName;
}
}
Bye
Hans
Hans Poo, Welinux S.A.
Bombero Ossa #1010, oficina 526,
+56-2-3729770, Movil: +56-9-3199305
Santiago, Chile
--
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] Date conversion using day of week
On Tuesday, March 29, 2011 8:33:59 am Steve Crawford wrote:
> On 03/29/2011 08:07 AM, Marc Munro wrote:
> > I'm trying to validate a day of the week, and thought that to_date would
> > do the job for me. But I found a case where it cannot tell the
> > difference between sunday and monday. Is this a bug or intended
> > behaviour?
> >
> > dev=# select to_date('2011-13-Mon', '-IW-DY');
> >
> >to_date
> >
> >
> >
> > 2011-03-28
>
> Based on running the queries in 9.0, it's behavior that has been corrected:
>
> select to_date('Mon1-13-Tue', '-IW-DY');
> ERROR: invalid combination of date conventions
> HINT: Do not mix Gregorian and ISO week date conventions in a
> formatting template.
>
> Cheers,
> Steve
Yes and no:)
test(5432)aklaver=>select to_date('2011-13-MON', 'IYYY-IW-DY');
to_date
2011-03-28
(1 row)
test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
(1 row)
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Date conversion using day of week
On 03/29/2011 08:50 AM, Adrian Klaver wrote:
On Tuesday, March 29, 2011 8:33:59 am Steve Crawford wrote:
On 03/29/2011 08:07 AM, Marc Munro wrote:
I'm trying to validate a day of the week, and thought that to_date would
do the job for me. But I found a case where it cannot tell the
difference between sunday and monday. Is this a bug or intended
behaviour?
dev=# select to_date('2011-13-Mon', '-IW-DY');
to_date
2011-03-28
Based on running the queries in 9.0, it's behavior that has been corrected:
select to_date('Mon1-13-Tue', '-IW-DY');
ERROR: invalid combination of date conventions
HINT: Do not mix Gregorian and ISO week date conventions in a
formatting template.
Cheers,
Steve
Yes and no:)
test(5432)aklaver=>select to_date('2011-13-MON', 'IYYY-IW-DY');
to_date
2011-03-28
(1 row)
test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
(1 row)
But you changed it to specify an ISO year avoiding the mixed
conventions. According to the 9.0 docs
(http://www.postgresql.org/docs/9.0/static/functions-formatting.html):
"An ISO week date (as distinct from a Gregorian date) can be specified
to to_timestamp and to_date in one of two ways:
Year, week, and weekday: for example to_date('2006-42-4',
'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it is
assumed to be 1 (Monday).
Year and day of year: for example to_date('2006-291',
'IYYY-IDDD') also returns 2006-10-19.
Attempting to construct a date using a mixture of ISO week and Gregorian
date fields is nonsensical, and will cause an error. In the context of
an ISO year, the concept of a "month" or "day of month" has no meaning.
In the context of a Gregorian year, the ISO week has no meaning. Users
should avoid mixing Gregorian and ISO date specifications. "
So I guess the upshot is that 9.0 throws errors on mixed input, but the
OP's issues can probably be resolved by explicitly specifying an ISO
year in the formatting.
Cheers,
Steve
--
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-9.0 service starting problem
Kalai, > > "The postgresql-9.0 service on Local Computer started and then stopped. > Some services stop automatically if they have no work to do, for example, > the Performance Logs and Alerts Service" > > most likely problem are unavailable ressources, as in: - PostgreSQL cannot access its data directory (because of changed file/directory permissions) - PostgreSQL cannot open its port for communication (because of other running PostgreSQL / because of zealous firewalls) or wrong configuration files, i.e. errors in pg_hba.conf or postgresql.conf. Start the eventview application and check for entries in the application log. Best wishes Harald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH GF: Harald Armin Massa Amtsgericht Stuttgart, HRB 736399
[GENERAL] OSX Postgres PL/Perl Problem with Finance::Quote/Options
Hi@all,
I have a weird problem on dev server (using a Mac Mini 2010 Edt., 10.6.7 OSX).
I use two Perl modules Finance::Quote and Finance::QuoteOptions in a stored
PL/Perl procedure which run without problems on a standard LINUX environment
(currently in production on CentOS5). Unfortunately on OSX it seems not to be
able to properly connect and retrieve the data from Yahoo.
Using the same code in a local perl file (under db user) it works without
problems, but fails in the stored procedure. I don't get any compilation errors
during import or execution and also no exceptions while the code is executed.
As mentioned, the same function works fine under Linux.
Environment:
- OSX 10.6.7
- Postgres 9.0.2 (compiled from source / config options --with-openssl
--with-perl --with-libraries=/opt/local/lib --with-includes=/opt/local/include
/ readline was installed via ports)
- Perl 5.12.3 (compled from source under /usr/local/ linked via enviromentable
variables in .profile / config options ./configure.gnu -des -Duseshrplib
--prefix=/usr/local/perl5)
I guess this is some weird behaviour under OSX (one of many) and hopefully
somebody stumbled over this already in the past.
Thank you in advance!
cheers,
Alex
Here is a condensed version of the function to illustrate the problem:
CREATE FUNCTION func_test() RETURNS SETOF maxpain AS $$
use strict;
#use warnings;
use Finance::Quote;
use Finance::QuoteOptions;
use Time::localtime;
elog(INFO, "Starting Yahoo data retrieval for: C;");
# YAHOO! DATA RETRIEVAL & PREPARATION
# Variables
my @work_array; # Main work array filled with final & calculated values
my $_stock_symbol = 'C';
# Get Quote Information
my $q = Finance::Quote->new();
my %data = $q->fetch('nyse', 'C');
if (!defined %data) { elog(INFO, "Quote conn. error;");
#return_next({status=>3});
return undef;
}
# Get Quote Options (Put OI, Call OI, Strike Put/Call etc.)
my $qf=Finance::QuoteOptions->new('C');
if (!$qf->retrieve) { elog(INFO, "QuoteOptions conn. errors");
return undef;
}
$$ LANGUAGE plperlu;
[GENERAL] Perl script to drop duplicated constraints definitions
Hi,
Sorry for the recent post, mistakenly i wrote the subject in spanish... glup.
Hi,
Sometimes i end up with some duplicated constraints definitions in my database,
i've noticed this when i reverse engineer databases, and see many links between
two tables.
I prepared a perl script that read an schema on standard input, and prints on
standard output some drop constraints for duplicated definitions, if you like
you can then execute them against your database:
For example if you save it as drop-dup-constraints.pl, then you can check your
database with:
pg_dump -Ox -s mydatabase | drop-dup-constraints.pl
#!/usr/bin/perl
use strict;
use warnings;
## Elimina los constraints sobre el mismo campo y la misma tabla
my ($table, $constName, $field);
my %tuplas;
my @lines = ();
while (<>) {
next if /--/;
chomp;
push @lines, $_;
# Ensamblar el sql acumulado e imprimir
if (/;/) {
&processLine(join " ", @lines);
@lines = ();
}
}
# Results
TUPLA: while (my ($k,$v) = each %tuplas) {
next TUPLA if @$v == 1;
#print "$k\n";
my @arr = @$v;
shift @arr; # let the first constraint
my ($table) = split /,/, $k;
foreach my $constraint (@arr) {
# print "\t$_\n";
printf "alter table %s drop constraint %s;\n", $table, $constraint;
}
}
exit 0;
my $lastSchema = "public";
sub processLine {
local $_ = shift;
chomp;
$lastSchema = $1 if /SET search_path = (\w+)/;
if (my ($table, $constName, $constraintType, $constraintName) = /ALTER TABLE
ONLY (\w+)\s+ADD CONSTRAINT (\w+) (FOREIGN KEY|UNIQUE)\s*\((\w+)\)/) {
my $key = "$lastSchema.$table,$constraintType.$constraintName";
my $aref = $tuplas{$key};
unless ($aref) {
$aref = [];
$tuplas{$key} = $aref;
}
push @$aref, $constName;
}
}
Bye
Hans
Hans Poo, Welinux S.A.
Bombero Ossa #1010, oficina 526,
+56-2-3729770, Movil: +56-9-3199305
Santiago, Chile
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UTF8 conversion revisited
So, we are still having an issue with this and I thought I'd throw this out to the list to see if I'm missing something. Basically, we have identified the tables/fields we need to convert. I'm running the following perl code against the fields and re-inserting the 'fixed' code into the field: data =~ s/(.)/((ord($1) >= 0) && (ord($1) <= 8)) || (ord($1) == 11) || ((ord($1) >= 13) && (ord($1) <= 31)) || ((ord($1) >= 127)) ?"": $1/egs; This appears to be working as a large number of records are cleaned. Problem is, someone it's not fixing data that contains the hex value 0xbd, as when I attempt to dump this database and create a new one with the UTF8 encoding I get the following error: pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 5246; 0 4978675 TABLE DATA cust postgres pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xbd As I see it, the perl code above should catch this '0xbd' character, but somehow it is finding it's way through. Any insights would be greatly appreciated. -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Curious case of the unstoppable user
Hi all, I've just set up a test user, revoked all access from them to a database, then tried to connect to that database and it let me in. When I try it all from scratch, it works correctly. Here's the set running correctly: postgres=# CREATE DATABASE testdb; CREATE DATABASE postgres=# CREATE ROLE testrole; CREATE ROLE postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE; REVOKE postgres=# \c testdb testrole FATAL: role "testrole" is not permitted to log in Previous connection kept But now if I try something similar with an existing user and existing database, it doesn't work: postgres=# REVOKE ALL ON DATABASE stuff FROM meow CASCADE; REVOKE postgres=# \c stuff meow You are now connected to database "stuff" as user "meow". So, I'm overlooking something. Could someone tell me what it is? I bet it's something obvious. I'm using 9.1dev if it's relevant. Thanks -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 could keep a connection / query alive?
On Tue, Mar 29, 2011 at 3:17 AM, Jerry Sievers wrote: > > > What can cause this?? Why would these tcp and statement timeout settings > not terminate the backend? > > Try trussing the backend process. You may find it in a network IO wait > trying to send data to a client that is hung or over a socket that was > timed out by a firewall or network equipment. > > Such a condition will cause the backend to be unable to hear the > cancel. The statement will still show as running in pg_stat_activity. > > SIGTERM on such a backend will probably also fall on deaf ears. I'm aware of that condition, which is exactly what the keepalive settings are supposed to detect. # strace -p 32307 Process 32307 attached - interrupt to quit send(6, "\252\0\17\0\0\0\01042810425\0\0\0\01010010333\0\0\0\27"..., 880, 0 The client is remote (not unix-domain socket) so I expect tcp_keepalive settings to kill this connection after 32 minutes. That's not happening. Not sure where else to look. Derrick
Re: [GENERAL] Curious case of the unstoppable user
On 29/03/2011 19:44, Thom Brown wrote: Hi all, I've just set up a test user, revoked all access from them to a database, then tried to connect to that database and it let me in. When I try it all from scratch, it works correctly. Here's the set running correctly: postgres=# CREATE DATABASE testdb; CREATE DATABASE postgres=# CREATE ROLE testrole; CREATE ROLE postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE; REVOKE postgres=# \c testdb testrole FATAL: role "testrole" is not permitted to log in Previous connection kept But now if I try something similar with an existing user and existing database, it doesn't work: postgres=# REVOKE ALL ON DATABASE stuff FROM meow CASCADE; REVOKE postgres=# \c stuff meow You are now connected to database "stuff" as user "meow". So, I'm overlooking something. Could someone tell me what it is? I bet it's something obvious. I'm using 9.1dev if it's relevant. Does the "public" role still have privileges on the database? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious case of the unstoppable user
On 03/29/2011 11:44 AM, Thom Brown wrote: Hi all, I've just set up a test user, revoked all access from them to a database, then tried to connect to that database and it let me in. When I try it all from scratch, it works correctly. Here's the set running correctly: postgres=# CREATE DATABASE testdb; CREATE DATABASE postgres=# CREATE ROLE testrole; CREATE ROLE postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE; REVOKE postgres=# \c testdb testrole FATAL: role "testrole" is not permitted to log in Previous connection kept But now if I try something similar with an existing user and existing database, it doesn't work: postgres=# REVOKE ALL ON DATABASE stuff FROM meow CASCADE; REVOKE postgres=# \c stuff meow You are now connected to database "stuff" as user "meow". So, I'm overlooking something. Could someone tell me what it is? I bet it's something obvious. I'm using 9.1dev if it's relevant. Thanks Not sure, but is user "meow" either the owner of the database "stuff" or member of a group that permits access to "stuff"? Cheers, Steve -- 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] Curious case of the unstoppable user
On 29 March 2011 21:06, Raymond O'Donnell wrote:
> On 29/03/2011 19:44, Thom Brown wrote:
>>
>> Hi all,
>>
>> I've just set up a test user, revoked all access from them to a
>> database, then tried to connect to that database and it let me in.
>> When I try it all from scratch, it works correctly.
>>
>> Here's the set running correctly:
>>
>> postgres=# CREATE DATABASE testdb;
>> CREATE DATABASE
>> postgres=# CREATE ROLE testrole;
>> CREATE ROLE
>> postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE;
>> REVOKE
>> postgres=# \c testdb testrole
>> FATAL: role "testrole" is not permitted to log in
>> Previous connection kept
>>
>> But now if I try something similar with an existing user and existing
>> database, it doesn't work:
>>
>> postgres=# REVOKE ALL ON DATABASE stuff FROM meow CASCADE;
>> REVOKE
>> postgres=# \c stuff meow
>> You are now connected to database "stuff" as user "meow".
>>
>> So, I'm overlooking something. Could someone tell me what it is? I
>> bet it's something obvious. I'm using 9.1dev if it's relevant.
>
>
> Does the "public" role still have privileges on the database?
The access privileges shown on both databases are identical: "=Tc/thom
thom=CTc/thom", and both owned by user "thom".
Both users meow and testrole show blank membership: "{}"
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] Curious case of the unstoppable user
On Tue, Mar 29, 2011 at 07:44:51PM +0100, Thom Brown wrote: > So, I'm overlooking something. Could someone tell me what it is? I > bet it's something obvious. I'm using 9.1dev if it's relevant. perhaps meow is superuser? Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious case of the unstoppable user
On 29 March 2011 21:28, hubert depesz lubaczewski wrote:
> On Tue, Mar 29, 2011 at 07:44:51PM +0100, Thom Brown wrote:
>> So, I'm overlooking something. Could someone tell me what it is? I
>> bet it's something obvious. I'm using 9.1dev if it's relevant.
>
> perhaps meow is superuser?
stuff=> \dg+
List of roles
Role name | Attributes | Member
of | Description
---++---+-
meow || {}|
testrole | Cannot login | {}|
thom | Superuser, Create role, Create DB, Replication | {}|
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] Curious case of the unstoppable user
On 03/29/2011 01:32 PM, Thom Brown wrote:
On 29 March 2011 21:28, hubert depesz lubaczewski wrote:
On Tue, Mar 29, 2011 at 07:44:51PM +0100, Thom Brown wrote:
So, I'm overlooking something. Could someone tell me what it is? I
bet it's something obvious. I'm using 9.1dev if it's relevant.
perhaps meow is superuser?
stuff=> \dg+
List of roles
Role name | Attributes | Member
of | Description
---++---+-
meow || {}|
testrole | Cannot login | {}|
thom | Superuser, Create role, Create DB, Replication | {}|
My guess is you have pg_hba.conf set up to use trust for the connection.
In your original example testrole failed because it is not a login role
not for permissions reasons. When \c to stuff as meow can you do \d?
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] RPM for ODBC driver
Hi, I am looking for an RPM to install the ODBC driver for PostgreSQL 9.0.2. The repository contains quite a few RPMs but only the source code for the ODBC driver. Is there another place I can look for an RPM for this? Thanks, Craig
Re: [GENERAL] Curious case of the unstoppable user
On 29 March 2011 21:51, Adrian Klaver wrote:
> On 03/29/2011 01:32 PM, Thom Brown wrote:
>>
>> On 29 March 2011 21:28, hubert depesz lubaczewski
>> wrote:
>>>
>>> On Tue, Mar 29, 2011 at 07:44:51PM +0100, Thom Brown wrote:
So, I'm overlooking something. Could someone tell me what it is? I
bet it's something obvious. I'm using 9.1dev if it's relevant.
>>>
>>> perhaps meow is superuser?
>>
>> stuff=> \dg+
>> List of roles
>> Role name | Attributes | Member
>> of | Description
>>
>> ---++---+-
>> meow | | {} |
>> testrole | Cannot login | {} |
>> thom | Superuser, Create role, Create DB, Replication | {} |
>>
>
> My guess is you have pg_hba.conf set up to use trust for the connection. In
> your original example testrole failed because it is not a login role not for
> permissions reasons. When \c to stuff as meow can you do \d?
I can do \d, but it doesn't show anything since there's nothing in
there. But it does let me create a table, then see it using \d...
stuff=> \c stuff meow
You are now connected to database "stuff" as user "meow".
stuff=> \d
No relations found.
stuff=> create table test (id serial);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
CREATE TABLE
stuff=> \d
List of relations
Schema |Name | Type | Owner
+-+--+---
public | test| table| meow
public | test_id_seq | sequence | meow
(2 rows)
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] Curious case of the unstoppable user
Le 29/03/2011 20:44, Thom Brown a écrit :
> Hi all,
>
> I've just set up a test user, revoked all access from them to a
> database, then tried to connect to that database and it let me in.
> When I try it all from scratch, it works correctly.
>
> Here's the set running correctly:
>
> postgres=# CREATE DATABASE testdb;
> CREATE DATABASE
> postgres=# CREATE ROLE testrole;
> CREATE ROLE
> postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE;
> REVOKE
> postgres=# \c testdb testrole
> FATAL: role "testrole" is not permitted to log in
> Previous connection kept
>
This is because you created a role without the login attribute. IOW, it
has nothing to do with your REVOKE statement. Proof:
postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# CREATE ROLE testrole;
CREATE ROLE
postgres=# \c testdb testrole
FATAL: role "testrole" is not permitted to log in
Previous connection kept
> But now if I try something similar with an existing user and existing
> database, it doesn't work:
>
> postgres=# REVOKE ALL ON DATABASE stuff FROM meow CASCADE;
> REVOKE
> postgres=# \c stuff meow
> You are now connected to database "stuff" as user "meow".
>
> So, I'm overlooking something. Could someone tell me what it is? I
> bet it's something obvious. I'm using 9.1dev if it's relevant.
>
Yeah. You probably created meow as a user, with is a role with the login
attribute. The \dg+ metacommand tells us exactly that:
> stuff=> \dg+
> List of roles
> Role name | Attributes | Member
> of | Description
> ---++---+-
> meow || {}|
> testrole | Cannot login | {}|
> thom | Superuser, Create role, Create DB, Replication | {}|
So:
postgres=# CREATE USER meow;
CREATE ROLE
postgres=# \c testdb meow
You are now connected to database "testdb" as user "meow".
Now, you not only need to revoke connect permission to meow. You need to
do it to public too:
testdb=> \c testdb postgres
You are now connected to database "testdb" as user "postgres".
testdb=# REVOKE ALL ON DATABASE testdb FROM meow CASCADE;
REVOKE
testdb=# \c testdb meow
You are now connected to database "testdb" as user "meow".
Same result as you. Now, revoke connect permission to public:
testdb=> \c testdb postgres
You are now connected to database "testdb" as user "postgres".
testdb=# REVOKE ALL ON DATABASE testdb FROM PUBLIC CASCADE;
REVOKE
testdb=# \c testdb meow
FATAL: permission denied for database "testdb"
DETAIL: User does not have CONNECT privilege.
Previous connection kept
Cheers.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious case of the unstoppable user
On 29 March 2011 21:59, Guillaume Lelarge wrote:
> Le 29/03/2011 20:44, Thom Brown a écrit :
>> Hi all,
>>
>> I've just set up a test user, revoked all access from them to a
>> database, then tried to connect to that database and it let me in.
>> When I try it all from scratch, it works correctly.
>>
>> Here's the set running correctly:
>>
>> postgres=# CREATE DATABASE testdb;
>> CREATE DATABASE
>> postgres=# CREATE ROLE testrole;
>> CREATE ROLE
>> postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE;
>> REVOKE
>> postgres=# \c testdb testrole
>> FATAL: role "testrole" is not permitted to log in
>> Previous connection kept
>>
>
> This is because you created a role without the login attribute. IOW, it
> has nothing to do with your REVOKE statement. Proof:
>
> postgres=# CREATE DATABASE testdb;
> CREATE DATABASE
> postgres=# CREATE ROLE testrole;
> CREATE ROLE
> postgres=# \c testdb testrole
> FATAL: role "testrole" is not permitted to log in
> Previous connection kept
>
>> But now if I try something similar with an existing user and existing
>> database, it doesn't work:
>>
>> postgres=# REVOKE ALL ON DATABASE stuff FROM meow CASCADE;
>> REVOKE
>> postgres=# \c stuff meow
>> You are now connected to database "stuff" as user "meow".
>>
>> So, I'm overlooking something. Could someone tell me what it is? I
>> bet it's something obvious. I'm using 9.1dev if it's relevant.
>>
>
> Yeah. You probably created meow as a user, with is a role with the login
> attribute. The \dg+ metacommand tells us exactly that:
>
>> stuff=> \dg+
>> List of roles
>> Role name | Attributes | Member
>> of | Description
>> ---++---+-
>> meow | | {} |
>> testrole | Cannot login | {} |
>> thom | Superuser, Create role, Create DB, Replication | {} |
>
> So:
>
> postgres=# CREATE USER meow;
> CREATE ROLE
> postgres=# \c testdb meow
> You are now connected to database "testdb" as user "meow".
>
> Now, you not only need to revoke connect permission to meow. You need to
> do it to public too:
>
> testdb=> \c testdb postgres
> You are now connected to database "testdb" as user "postgres".
> testdb=# REVOKE ALL ON DATABASE testdb FROM meow CASCADE;
> REVOKE
> testdb=# \c testdb meow
> You are now connected to database "testdb" as user "meow".
>
> Same result as you. Now, revoke connect permission to public:
>
> testdb=> \c testdb postgres
> You are now connected to database "testdb" as user "postgres".
> testdb=# REVOKE ALL ON DATABASE testdb FROM PUBLIC CASCADE;
> REVOKE
> testdb=# \c testdb meow
> FATAL: permission denied for database "testdb"
> DETAIL: User does not have CONNECT privilege.
> Previous connection kept
I altered the role with NOLOGIN, then tried to connect as that user
again, and it doesn't let the user in, so you're correct.
Thanks for the explanation. I take it the access priviledges field
shown in \l+ reveals this? It must be the line that begins with =. I
need to familiarise myself with it more.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] Curious case of the unstoppable user
Le 29/03/2011 23:12, Thom Brown a écrit :
> On 29 March 2011 21:59, Guillaume Lelarge wrote:
>> Le 29/03/2011 20:44, Thom Brown a écrit :
>>> Hi all,
>>>
>>> I've just set up a test user, revoked all access from them to a
>>> database, then tried to connect to that database and it let me in.
>>> When I try it all from scratch, it works correctly.
>>>
>>> Here's the set running correctly:
>>>
>>> postgres=# CREATE DATABASE testdb;
>>> CREATE DATABASE
>>> postgres=# CREATE ROLE testrole;
>>> CREATE ROLE
>>> postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE;
>>> REVOKE
>>> postgres=# \c testdb testrole
>>> FATAL: role "testrole" is not permitted to log in
>>> Previous connection kept
>>>
>>
>> This is because you created a role without the login attribute. IOW, it
>> has nothing to do with your REVOKE statement. Proof:
>>
>> postgres=# CREATE DATABASE testdb;
>> CREATE DATABASE
>> postgres=# CREATE ROLE testrole;
>> CREATE ROLE
>> postgres=# \c testdb testrole
>> FATAL: role "testrole" is not permitted to log in
>> Previous connection kept
>>
>>> But now if I try something similar with an existing user and existing
>>> database, it doesn't work:
>>>
>>> postgres=# REVOKE ALL ON DATABASE stuff FROM meow CASCADE;
>>> REVOKE
>>> postgres=# \c stuff meow
>>> You are now connected to database "stuff" as user "meow".
>>>
>>> So, I'm overlooking something. Could someone tell me what it is? I
>>> bet it's something obvious. I'm using 9.1dev if it's relevant.
>>>
>>
>> Yeah. You probably created meow as a user, with is a role with the login
>> attribute. The \dg+ metacommand tells us exactly that:
>>
>>> stuff=> \dg+
>>> List of roles
>>> Role name | Attributes | Member
>>> of | Description
>>> ---++---+-
>>> meow || {}|
>>> testrole | Cannot login | {}|
>>> thom | Superuser, Create role, Create DB, Replication | {}|
>>
>> So:
>>
>> postgres=# CREATE USER meow;
>> CREATE ROLE
>> postgres=# \c testdb meow
>> You are now connected to database "testdb" as user "meow".
>>
>> Now, you not only need to revoke connect permission to meow. You need to
>> do it to public too:
>>
>> testdb=> \c testdb postgres
>> You are now connected to database "testdb" as user "postgres".
>> testdb=# REVOKE ALL ON DATABASE testdb FROM meow CASCADE;
>> REVOKE
>> testdb=# \c testdb meow
>> You are now connected to database "testdb" as user "meow".
>>
>> Same result as you. Now, revoke connect permission to public:
>>
>> testdb=> \c testdb postgres
>> You are now connected to database "testdb" as user "postgres".
>> testdb=# REVOKE ALL ON DATABASE testdb FROM PUBLIC CASCADE;
>> REVOKE
>> testdb=# \c testdb meow
>> FATAL: permission denied for database "testdb"
>> DETAIL: User does not have CONNECT privilege.
>> Previous connection kept
>
> I altered the role with NOLOGIN, then tried to connect as that user
> again, and it doesn't let the user in, so you're correct.
>
> Thanks for the explanation. I take it the access priviledges field
> shown in \l+ reveals this? It must be the line that begins with =. I
> need to familiarise myself with it more.
>
it doesn't tell you about the LOGIN attribute, but it tells you about
the priviledges, and among them, the CONNECT one. When the line begins
with =, it's the priviledges for public. If you have a user name before
the equal sign, then it's the priviledges for this user. For example:
testdb=# grant connect on database testdb to testrole;
GRANT
testdb=# \l+
Name |Access privileges
--+-
b1 |
testdb | guillaume=CTc/guillaume+
testrole=c/guillaume
On b1, anyone with the LOGIN attribute can connect. On testdb, only
guillaume and testrole can connect, but only guillaume can create objects.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curious case of the unstoppable user
On Mar 30, 2011, at 12:14 AM, Thom Brown wrote: > postgres=# CREATE ROLE testrole; > CREATE ROLE > postgres=# REVOKE ALL ON DATABASE testdb FROM testrole CASCADE; > REVOKE > postgres=# \c testdb testrole > FATAL: role "testrole" is not permitted to log in > Previous connection kept You have created role and trying to login as role. Create user and then try. USER=ROLE+Login Privilege. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OSX Postgres PL/Perl Problem with Finance::Quote/Options
On Mar 29, 2011, at 9:11 PM, Peter Pan wrote: > I use two Perl modules Finance::Quote and Finance::QuoteOptions in a stored > PL/Perl procedure which run without problems on a standard LINUX environment > (currently in production on CentOS5). Unfortunately on OSX it seems not to be > able to properly connect and retrieve the data from Yahoo. > > Using the same code in a local perl file (under db user) it works without > problems, but fails in the stored procedure. I don't get any compilation > errors during import or execution and also no exceptions while the code is > executed. As mentioned, the same function works fine under Linux. > > Environment: > - OSX 10.6.7 > - Postgres 9.0.2 (compiled from source / config options --with-openssl > --with-perl --with-libraries=/opt/local/lib > --with-includes=/opt/local/include / readline was installed via ports) > - Perl 5.12.3 (compled from source under /usr/local/ linked via enviromentab > le variables in .profile / config options ./configure.gnu -des -Duseshrplib > --prefix=/usr/local/perl5) Any Error Message? Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RPM for ODBC driver
On Mar 30, 2011, at 12:43 AM, Worgan, Craig (Craig) wrote: > I am looking for an RPM to install the ODBC driver for PostgreSQL 9.0.2. The > repository contains quite a few RPMs but only the source code for the ODBC > driver. Is there another place I can look for an RPM for this? Try Following link: http://rpm.pbone.net/index.php3/stat/4/idpl/14282865/dir/opensuse/com/psqlODBC-08.03.0200-9.2.i586.rpm.html Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RPM for ODBC driver
On 03/29/11 12:13 PM, Worgan, Craig (Craig) wrote: Hi, I am looking for an RPM to install the ODBC driver for PostgreSQL 9.0.2. The repository contains quite a few RPMs but only the source code for the ODBC driver. Is there another place I can look for an RPM for this? you are aware, most RPMs are generally distribution/version specific? an RPM for RHEL5 doesn't work on RHEL4 or Fedora anything or Suse, etc etc. -- 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 could keep a connection / query alive?
On Tue, Mar 29, 2011 at 2:54 PM, Derrick Rice wrote: > >> Try trussing the backend process. You may find it in a network IO wait >> trying to send data to a client that is hung or over a socket that was >> timed out by a firewall or network equipment. >> >> Such a condition will cause the backend to be unable to hear the >> cancel. The statement will still show as running in pg_stat_activity. >> >> SIGTERM on such a backend will probably also fall on deaf ears. > > > I'm aware of that condition, which is exactly what the keepalive settings > are supposed to detect. > So I spent some time reading Linux-2.6 TCP code and my previous statement is downright wrong. Keepalive is only in use when there is no data unacknowledged and no data to send. Retransmission timeouts are in use for those other scenarios. In any case, I would have expected a retransmission timeout. My new hypothesis based on output from `ss' is that a firewall, NAT, or VPN of my users is putting the connection into persist mode (setting the window size to 0) when the end point of the connection is unresponsive. Furthermore, I think that firewall is continuing to respond to the persist probes of my machine until it finally decides that the end point is gone. At which point it might be ignoring future probes, starting the retransmission timeouts for my machine. So I'm not looking for any further help here, since this isn't a PostgreSQL issue. If I resolve the problem I'll let you all know just for entertainment purposes :) Thanks Derrick
[GENERAL] Totally new, two main problems.
Im learning pg sql and psql in general. Im using console psql in windows. For now, i installed it correctly and execute it, ok. My first problem is that i dont know how to execute a pgsql script when im in the psql program. CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql; This is in c:/2tri/scr.sql. How i can execute that? Also my other problem is with pgsql syntax. How i can print a text to the console for checking, debugign, etc, i dont see anything to do that. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Totally-new-two-main-problems-tp4270202p4270202.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Date conversion using day of week
On Tuesday, March 29, 2011 9:02:52 am Steve Crawford wrote:
>
> But you changed it to specify an ISO year avoiding the mixed
> conventions. According to the 9.0 docs
> (http://www.postgresql.org/docs/9.0/static/functions-formatting.html):
>
> "An ISO week date (as distinct from a Gregorian date) can be specified
> to to_timestamp and to_date in one of two ways:
>
>Year, week, and weekday: for example to_date('2006-42-4',
> 'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it is
> assumed to be 1 (Monday).
>
>Year and day of year: for example to_date('2006-291',
> 'IYYY-IDDD') also returns 2006-10-19.
>
> Attempting to construct a date using a mixture of ISO week and Gregorian
> date fields is nonsensical, and will cause an error. In the context of
> an ISO year, the concept of a "month" or "day of month" has no meaning.
> In the context of a Gregorian year, the ISO week has no meaning. Users
> should avoid mixing Gregorian and ISO date specifications. "
>
> So I guess the upshot is that 9.0 throws errors on mixed input, but the
> OP's issues can probably be resolved by explicitly specifying an ISO
> year in the formatting.
>
> Cheers,
> Steve
Well the strange part is only fails for SUN:
test(5432)aklaver=>select to_date('2011-13-MON', 'IYYY-IW-DY');
to_date
2011-03-28
(1 row)
test(5432)aklaver=>select to_date('2011-13-TUE', 'IYYY-IW-DY');
to_date
2011-03-29
(1 row)
test(5432)aklaver=>select to_date('2011-13-WED', 'IYYY-IW-DY');
to_date
2011-03-30
(1 row)
test(5432)aklaver=>select to_date('2011-13-THU', 'IYYY-IW-DY');
to_date
2011-03-31
(1 row)
test(5432)aklaver=>select to_date('2011-13-FRI', 'IYYY-IW-DY');
to_date
2011-04-01
(1 row)
test(5432)aklaver=>select to_date('2011-13-SAT', 'IYYY-IW-DY');
to_date
2011-04-02
(1 row)
test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
Agreed, maintaining ISO arguments across the board is the way to go:
Monday
select to_date('2011-13-1', 'IYYY-IW-ID');
to_date
2011-03-28
Sunday
select to_date('2011-13-7', 'IYYY-IW-ID');
to_date
2011-04-03
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Totally new, two main problems.
On Tuesday, March 29, 2011 2:20:33 pm wepwep wrote: > Im learning pg sql and psql in general. Im using console psql in windows. > For now, i installed it correctly and execute it, ok. > > My first problem is that i dont know how to execute a pgsql script when im > in the psql program. > CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ > BEGIN > RETURN subtotal * 0.06; > END; > $$ LANGUAGE plpgsql; > > > This is in c:/2tri/scr.sql. How i can execute that? >From in psql> \i c:/2tri/scr.sql will load the function into the database, assuming the user you are logged in has appropriate permissions. FYI I would use the form CREATE OR REPLACE FUNCTION. This allows you change an existing function without dropping it. Then do : select sales_tax(150.24); > > Also my other problem is with pgsql syntax. How i can print a text to the > console for checking, debugign, etc, i dont see anything to do that. See here: http://www.postgresql.org/docs/9.0/interactive/plpgsql-errors-and-messages.html If you are using 9.0+ you might also want to check out DO: http://www.postgresql.org/docs/9.0/interactive/sql-do.html > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Totally-new-two-main-problems-tp42 > 70202p4270202.html Sent from the PostgreSQL - general mailing list archive > at Nabble.com. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RPM for ODBC driver
On Tue, 2011-03-29 at 15:13 -0400, Worgan, Craig (Craig) wrote: > > I am looking for an RPM to install the ODBC driver for PostgreSQL > 9.0.2. did you take a look at http://yum.pgrpms.org/9.0/ ? Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Problem calling setweight function from JDBC
Thank you, this helped. Viliam On 24.3.2011 16:08, Tom Lane wrote: =?UTF-8?B?VmlsaWFtIMSOdXJpbmE=?= writes: I have a call to setweight function in a PreparedStatement with the following sql: update my_table set a_text_data=setweight(to_tsvector(? :: regconfig, ?), ? :: char) Make that ::"char" not ::char ... the quotes matter here, because char without quotes is a keyword. regards, tom lane -- 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] RPM for ODBC driver
No I didn't. That looks like what I'm looking for. Thanks! Craig -Original Message- From: Devrim GÜNDÜZ [mailto:dev...@gunduz.org] Sent: Tuesday, March 29, 2011 9:32 PM To: Worgan, Craig (Craig) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] RPM for ODBC driver On Tue, 2011-03-29 at 15:13 -0400, Worgan, Craig (Craig) wrote: > > I am looking for an RPM to install the ODBC driver for PostgreSQL > 9.0.2. did you take a look at http://yum.pgrpms.org/9.0/ ? Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
