[GENERAL] text column constraint, newbie question
Hi, I'm new to both PostgreSQL and web-based application development; I read the FAQ at postgresql.org (perhaps this discussion group has another FAQ that I haven't found yet?) and didn't see this addressed. I'm creating a table with a column of type text, to be used in a php web application, where I'll be accepting user input for that text field. Are there characters, maybe non-printing characters, or perhaps even whole phrases, that could cause problems in my database or application if I were to allow users to enter them into that column? If so, does anyone happen to have a regular expression handy that you think is a good choice for text columns' CHECK constraint? Or maybe a link to a discussion of this topic? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] (odbc) multiple step ole db generated error - date/timestamp column
zach cruise wrote: when importing from oracle 10g Importing how? CSV dump and load? DB link of some sort? Operating system and version? Oracle version? i get multiple step ole db generated error. From what program ? Where? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] text column constraint, newbie question
On Sat, Mar 21, 2009 at 11:13 PM, RebeccaJ rebec...@gmail.com wrote: Hi, I'm new to both PostgreSQL and web-based application development; I read the FAQ at postgresql.org (perhaps this discussion group has another FAQ that I haven't found yet?) and didn't see this addressed. I'm creating a table with a column of type text, to be used in a php web application, where I'll be accepting user input for that text field. Are there characters, maybe non-printing characters, or perhaps even whole phrases, that could cause problems in my database or application if I were to allow users to enter them into that column? If so, does anyone happen to have a regular expression handy that you think is a good choice for text columns' CHECK constraint? Or maybe a link to a discussion of this topic? Nope, there's nothing you can put into a text to break pgsql. However, if you are using regular old queries, you'd be advised to use pg_escape_string() function in php to prevent SQL injection attacks. -- 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] Breaking national language support in Solaris 8
Peter Eisentraut pete...@gmx.net writes: This will, however, only work with GNU Gettext (as used in Linux and BSD distributions) and Solaris 9 or later, and it is not easy to provide a backward compatible mode. Eh? I thought it was trivial to provide a backward compatible mode which is just as good as the existing code. Just use regular gettext on the two strings separately and pick the right one based on the English rule. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- 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 are the benefits of using a clustered index?
Simon Riggs si...@2ndquadrant.com writes: Just think one index tuple points to more than one heap row. Could you expand on that? Like, uh, I have no idea what you're saying. Less index pointers, smaller index. Are you talking about Heikkie's grouped-items-index? The trick is: How? But that's a secondary issue to getting it on the TODO list, which is all I'm suggesting at present. Well I think we need to be clear enough at least on the what if not the how. But there's a bit a of a fuzzy line between them I admit. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- 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] Breaking national language support in Solaris 8
On Sunday 22 March 2009 13:03:29 Gregory Stark wrote: Peter Eisentraut pete...@gmx.net writes: This will, however, only work with GNU Gettext (as used in Linux and BSD distributions) and Solaris 9 or later, and it is not easy to provide a backward compatible mode. Eh? I thought it was trivial to provide a backward compatible mode which is just as good as the existing code. Just use regular gettext on the two strings separately and pick the right one based on the English rule. Please follow the thread to the end. This message describes the main problem: http://archives.postgresql.org/message-id/49c25f76.3030...@gmx.net -- 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] (odbc) multiple step ole db generated error - date/timestamp column
On Sun, Mar 22, 2009 at 3:39 AM, Craig Ringer cr...@postnewspapers.com.au wrote: zach cruise wrote: when importing from oracle 10g Importing how? CSV dump and load? DB link of some sort? odbc (see email) specifically Microsoft OLE DB Provider for Oracle Operating system and version? Oracle version? windows 2k3 oracle 10g (see email) postgresql 8.1 (see email) i get multiple step ole db generated error. From what program ? Where? navicat (see email) again, i narrowed this down to a date/timestamp column that gets imported ok if imported as varchar. there is another date/timestamp column that gets imported error-free, and other tables also get imported ok. based on my narrowing-down, it appears less likely error is at os/odbc/postgresql/oracle level but more likely at database/table/column/row level (esp since all problem rows = '01-JAN-01' (never null) but that could be a false-alarm). -- 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] Srf function : missing library on PostgreSQL 8.3.6 on Windows?
Craig Ringer wrote :
If you're using C++ you must also:
- Ensure that no exceptions propagate outside your code
- Declare all hook functions that might be dlopen()ed as extern C
Thanks for your advices but I'm not sure to have understood. For example, when
I try to implement the normal_rand function from /contrib/tablefunc like
below, I have a server crash.
/**
* test.h *
**/
extern C {
#include postgres.h
#include fmgr.h
#include funcapi.h
#include executor/spi.h
#include lib/stringinfo.h
#include miscadmin.h
#include utils/builtins.h
#include utils/guc.h
#include utils/lsyscache.h
#include math.h
}
extern C __declspec (dllexport) Datum normal_rand(PG_FUNCTION_ARGS);
/*
* test.cpp *
*/
#include test.h
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(normal_rand);
Datum normal_rand(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
int call_cntr;
int max_calls;
normal_rand_fctx *fctx;
float8 mean;
float8 stddev;
float8 carry_val;
bool use_carry;
MemoryContext oldcontext;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
{
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();
/* switch to memory context appropriate for multiple function calls */
oldcontext = MemoryContextSwitchTo(funcctx-multi_call_memory_ctx);
/* total number of tuples to be returned */
funcctx-max_calls = PG_GETARG_UINT32(0);
/* allocate memory for user context */
fctx = (normal_rand_fctx *) palloc(sizeof(normal_rand_fctx));
/*
* Use fctx to keep track of upper and lower bounds from call to call.
* It will also be used to carry over the spare value we get from the
* Box-Muller algorithm so that we only actually calculate a new value
* every other call.
*/
fctx-mean = PG_GETARG_FLOAT8(1);
fctx-stddev = PG_GETARG_FLOAT8(2);
fctx-carry_val = 0;
fctx-use_carry = false;
funcctx-user_fctx = fctx;
MemoryContextSwitchTo(oldcontext);
}
/* stuff done on every call of the function */
funcctx = SRF_PERCALL_SETUP();
call_cntr = funcctx-call_cntr;
max_calls = funcctx-max_calls;
fctx = (normal_rand_fctx*) funcctx-user_fctx;
mean = fctx-mean;
stddev = fctx-stddev;
carry_val = fctx-carry_val;
use_carry = fctx-use_carry;
if (call_cntr max_calls) /* do when there is more left to send */
{
float8 result;
if (use_carry)
{
/* reset use_carry and use second value obtained on last pass */
fctx-use_carry = false;
result = carry_val;
}
else
{
float8 normval_1;
float8 normval_2;
/* Get the next two normal values */
get_normal_pair(normval_1, normval_2);
/* use the first */
result = mean + (stddev * normval_1);
/* and save the second */
fctx-carry_val = mean + (stddev * normval_2);
fctx-use_carry = true;
}
/* send the result */
SRF_RETURN_NEXT(funcctx, Float8GetDatum(result));
}
else
/* do when there is no more left */
SRF_RETURN_DONE(funcctx);
}
/*
* script *
*/
CREATE OR REPLACE FUNCTION normal_rand(int4, float8, float8)
RETURNS setof float8
AS '$libdir/tablefunc','normal_rand'
LANGUAGE C VOLATILE STRICT;
In the log file, it indicated that the server stop with an exception
(0xC005). In the ntstatus.h, it indicated that this exception 0xC005
means ACCESS VIOLATION. I don't know what causes this exception. It's also a
problem with some missing extern 'C' ? Or is it a null pointer problem ?
Tom Lane wrote :
On the whole I'd recommend using plain C for backend functions if you
possibly can.
Unfortunately, I must use cpp because I must translate an temporal extension
(wrote on cpp with templates, ...) from Oracle to PostgreSQL.
--
Ben Ali Rachid
Re: [GENERAL] Srf function : missing library on PostgreSQL 8.3.6 on Windows?
Ben Ali Rachid souliman...@yahoo.fr writes: Craig Ringer wrote : - Declare all hook functions that might be dlopen()ed as extern C Thanks for your advices but I'm not sure to have understood. PG_FUNCTION_INFO_V1() generates a function that has to have C linkage. So does PG_MODULE_MAGIC. I'm actually not sure how you got the module to load at all with the latter point ... 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] Srf function : missing library on PostgreSQL 8.3.6 on Windows?
Tom Lane wrote :
PG_FUNCTION_INFO_V1() generates a function that has to have C linkage.
So does PG_MODULE_MAGIC. I'm actually not sure how you got the module
to load at all with the latter point ...
Sorry, I forgot this in my previous post :
#ifdef __cplusplus
extern C {
#endif
--
#ifdef __cplusplus
}
#endif
I also forgot the definition of the function 'get_normal_pair'. So my (correct)
cpp file is like below :
#include dll.h
#ifdef __cplusplus
extern C {
#endif
PG_MODULE_MAGIC;
void get_normal_pair(float8 *x1, float8 *x2);
void get_normal_pair(float8 *x1, float8 *x2)
{
float8 u1,
u2,
v1,
v2,
s;
do
{
u1 = (float8) rand() / (float8) MAX_RANDOM_VALUE;
u2 = (float8) rand() / (float8) MAX_RANDOM_VALUE;
v1 = (2.0 * u1) - 1.0;
v2 = (2.0 * u2) - 1.0;
s = v1 * v1 + v2 * v2;
}
while (s = 1.0);
if (s == 0)
{
*x1 = 0;
*x2 = 0;
}
else
{
s = sqrt((-2.0 * log(s)) / s);
*x1 = v1 * s;
*x2 = v2 * s;
}
}
typedef struct
{
float8 mean; /* mean of the distribution */
float8 stddev; /* stddev of the distribution */
float8 carry_val; /* hold second generated value */
bool use_carry; /* use second generated value */
} normal_rand_fctx;
PG_FUNCTION_INFO_V1(normal_rand);
Datum normal_rand(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
int call_cntr;
int max_calls;
normal_rand_fctx *fctx;
float8 mean;
float8 stddev;
float8 carry_val;
bool use_carry;
MemoryContext oldcontext;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
{
/* create a function context for cross-call persistence */
funcctx = SRF_FIRSTCALL_INIT();
/* switch to memory context appropriate for multiple function calls. */
oldcontext = MemoryContextSwitchTo(funcctx-multi_call_memory_ctx);
/* total number of tuples to be returned */
funcctx-max_calls = PG_GETARG_UINT32(0);
/* allocate memory for user context */
fctx = (normal_rand_fctx *) palloc(sizeof(normal_rand_fctx));
/*
* Use fctx to keep track of upper and lower bounds from call to call.
* It will also be used to carry over the spare value we get from the
* Box-Muller algorithm so that we only actually calculate a new value
* every other call.
*/
fctx-mean = PG_GETARG_FLOAT8(1);
fctx-stddev = PG_GETARG_FLOAT8(2);
fctx-carry_val = 0;
fctx-use_carry = false;
funcctx-user_fctx = fctx;
MemoryContextSwitchTo(oldcontext);
}
/* stuff done on every call of the function */
funcctx = SRF_PERCALL_SETUP();
call_cntr = funcctx-call_cntr;
max_calls = funcctx-max_calls;
fctx = (normal_rand_fctx*) funcctx-user_fctx;
mean = fctx-mean;
stddev = fctx-stddev;
carry_val = fctx-carry_val;
use_carry = fctx-use_carry;
if (call_cntr max_calls) /* do when there is more left to send */
{
float8 result;
if (use_carry)
{
/* reset use_carry and use second value obtained on last pass */
fctx-use_carry = false;
result = carry_val;
}
else
{
float8 normval_1;
float8 normval_2;
/* Get the next two normal values */
get_normal_pair(normval_1, normval_2);
/* use the first */
result = mean + (stddev * normval_1);
/* and save the second */
fctx-carry_val = mean + (stddev * normval_2);
fctx-use_carry = true;
}
/* send the result */
SRF_RETURN_NEXT(funcctx, Float8GetDatum(result));
}
else
/* do when there is no more left */
SRF_RETURN_DONE(funcctx);
}
#ifdef __cplusplus
}
#endif
I have no problem when I load the DLL (no 'missing magic block' error). The
server crashes when I try : SELECT normal_rand(5, 10.0, 20.0) or SELECT * FROM
normal_rand(5, 10.0, 20.0).
--
Ben Ali Rachid
Re: [GENERAL] text column constraint, newbie question
Are there characters, maybe non-printing characters, or perhaps even whole phrases, that could cause problems in my database or application if I were to allow users to enter them into that column? If so, does anyone happen to have a regular expression handy that you think is a good choice for text columns' CHECK constraint? Or maybe a link to a discussion of this topic? Nope, there's nothing you can put into a text to break pgsql. However, if you are using regular old queries, you'd be advised to use pg_escape_string() function in php to prevent SQL injection attacks. Thanks! I'll check out pg_escape_string() in php, and I see that PostgreSQL also has something called PQescapeStringConn... I wonder if I should use both... Also, I should have asked: what about char and varchar fields? Can those also handle any characters, as long as I consider SQL injection attacks? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Understanding Execution Plans
I'm in the process of migrating a web application from a dedicated server to VPS Hosting (Slicehost). During the test phase I've spotted a huge performance advantage for the old dedicated server for some queries and I need some help interpreting the execution plans. Plan 1 - Dedicated Server Athlon 64 5000 - Debian 5.0 - 4GB Ram - 150 GB off the shelf Sata HD --- Limit (cost=16574.23..16574.28 rows=20 width=119) (actual time=466.140..466.158 rows=3 loops=1) - Sort (cost=16574.23..16574.29 rows=24 width=119) (actual time=466.135..466.141 rows=3 loops=1) Sort Key: c.total_achievement_points Sort Method: quicksort Memory: 25kB - Nested Loop Left Join (cost=86.99..16573.68 rows=24 width=119) (actual time=139.903..466.064 rows=3 loops=1) - Nested Loop Left Join (cost=86.99..16414.84 rows=24 width=108) (actual time=139.865..465.957 rows=3 loops=1) Join Filter: (c.class_id = classes.id) - Nested Loop Left Join (cost=86.99..16385.44 rows=24 width=86) (actual time=139.846..465.773 rows=3 loops=1) - Nested Loop Left Join (cost=86.99..16186.44 rows=24 width=73) (actual time=139.826..448.932 rows=3 loops=1) Join Filter: (c.race_id = races.id) - Nested Loop Left Join (cost=86.99..16157.04 rows=24 width=60) (actual time=139.775..448.750 rows=3 loops=1) Join Filter: (c.faction_id = factions.id) - Bitmap Heap Scan on characters c (cost=86.99..16128.72 rows=24 width=36) (actual time=139.721..448.574 rows=3 loops=1) Recheck Cond: (realm_id = 227) Filter: ((total_achievement_points 0) AND (level = 80)) - Bitmap Index Scan on characters_realm_id (cost=0.00..86.98 rows=4597 width=0) (actual time=26.076..26.076 rows=2028 loops=1) Index Cond: (realm_id = 227) - Seq Scan on faction_categories factions (cost=0.00..1.08 rows=8 width=28) (actual time=0.008..0.024 rows=8 loops=3) - Seq Scan on races (cost=0.00..1.10 rows=10 width=17) (actual time=0.004..0.025 rows=10 loops=3) - Index Scan using guilds_pkey on guilds g (cost=0.00..8.28 rows=1 width=17) (actual time=5.598..5.599 rows=1 loops=3) Index Cond: (c.guild_id = g.id) - Seq Scan on classes (cost=0.00..1.10 rows=10 width=26) (actual time=0.005..0.027 rows=10 loops=3) - Index Scan using realms_pkey on realms r (cost=0.00..6.61 rows=1 width=15) (actual time=0.018..0.022 rows=1 loops=3) Index Cond: ((r.id = 227) AND (c.realm_id = r.id)) Total runtime: 466.829 ms (25 rows) Plan 2 - Slicehost VPS 512 - Quadcore Opteron Xen VPS - Debian 5.0 - 512MB RAM - Raid 10 Storage on Host --- Limit (cost=17088.31..17088.36 rows=20 width=119) (actual time=5620.050..5620.050 rows=3 loops=1) - Sort (cost=17088.31..17088.37 rows=24 width=119) (actual time=5620.050..5620.050 rows=3 loops=1) Sort Key: c.total_achievement_points Sort Method: quicksort Memory: 25kB - Nested Loop Left Join (cost=92.10..17087.76 rows=24 width=119) (actual time=2016.018..5620.050 rows=3 loops=1) - Nested Loop Left Join (cost=92.10..16888.77 rows=24 width=106) (actual time=2016.018..5588.049 rows=3 loops=1) - Nested Loop Left Join (cost=92.10..16729.92 rows=24 width=95) (actual time=2016.018..5588.049 rows=3 loops=1) Join Filter: (c.class_id = classes.id) - Nested Loop Left Join (cost=92.10..16700.52 rows=24 width=73) (actual time=2016.018..5588.049 rows=3 loops=1) Join Filter: (c.race_id = races.id) - Nested Loop Left Join (cost=92.10..16671.12 rows=24 width=60) (actual time=2016.018..5588.049 rows=3 loops=1) Join Filter: (c.faction_id = factions.id) - Bitmap Heap Scan on characters c (cost=92.10..16642.80 rows=24 width=36) (actual time=2016.018..5588.049 rows=3 loops=1) Recheck Cond: (realm_id = 227) Filter: ((total_achievement_points 0) AND (level = 80)) - Bitmap Index Scan on characters_realm_id (cost=0.00..92.09 rows=4743 width=0) (actual time=76.001..76.001 rows=2033 loops=1) Index Cond: (realm_id = 227) - Seq Scan on faction_categories factions
Re: [GENERAL] Srf function : missing library on PostgreSQL 8.3.6 on Windows?
Ben Ali Rachid souliman...@yahoo.fr writes: I have no problem when I load the DLL (no 'missing magic block' error). The server crashes when I try : SELECT normal_rand(5, 10.0, 20.0) or SELECT * FROM normal_rand(5, 10.0, 20.0). Well, the next step would be to get out your debugger and try to identify exactly where it's crashing. 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] Understanding Execution Plans
Oliver Weichhold oli...@weichhold.com writes: It seems that especially the joins take extremely long on the VPS versus the dedicated machine but I'm not sure if that's caused by the the fact that the dedicated machine has 8x the amount of RAM and thus can cache much more data or because it has more I/O bandwidth due to the exclusive access to the harddisk or a combination of both. Any suggestions? I'd guess that your virtual machine is delivering seriously bad disk access performance. The relative lack of RAM certainly isn't helping though; if it had more then the kernel disk buffers could mask the poor I/O to some extent. You could perhaps fix the blame more accurately by doing some disk benchmarking with bonnie or a similar tool. 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] text column constraint, newbie question
On Sun, Mar 22, 2009 at 11:36 AM, RebeccaJ rebec...@gmail.com wrote: Are there characters, maybe non-printing characters, or perhaps even whole phrases, that could cause problems in my database or application if I were to allow users to enter them into that column? If so, does anyone happen to have a regular expression handy that you think is a good choice for text columns' CHECK constraint? Or maybe a link to a discussion of this topic? Nope, there's nothing you can put into a text to break pgsql. However, if you are using regular old queries, you'd be advised to use pg_escape_string() function in php to prevent SQL injection attacks. Thanks! I'll check out pg_escape_string() in php, and I see that PostgreSQL also has something called PQescapeStringConn... I wonder if I should use both... Isn't PGescapeStringConn a libpq function? I'm pretty sure that php's pg_escape_string is just calling that for you, so no need to use both. Also, I should have asked: what about char and varchar fields? Can those also handle any characters, as long as I consider SQL injection attacks? ayup. As long as they're legal for your encoding, they'll go right in. If you wanna stuff in anything no matter the encoding, use a database initialized for SQL_ASCII encoding. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] The tuple structure of a not-yet-assigned record is indeterminate.
Hi there, i'm new to postgres. I want to create view when adding new row. So
what i've got:
CREATE OR REPLACE FUNCTION add_view() RETURNS trigger AS $$
DECLARE
someint integer;
BEGIN
RAISE NOTICE 'dodajesz nowa lige %', NEW.id;
someint := NEW.id;
RAISE NOTICE 'dodajesz nowa lige %', someint;
CREATE VIEW tabelka AS SELECT * FROM t_matches.someint;
RETURN NULL;
END;
$$ language plpgsql;
CREATE TRIGGER league AFTER insert ON t_leagues FOR STATEMENT EXECUTE
PROCEDURE add_view();
Then in psql I made an query and got error:
league=# INSERT INTO t_leagues (name) VALUES('3liga');
ERROR: record new is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function add_view line 4 at RAISE
Whats wrong, I supposed that id is not reserverd at the moment, so what can
I do?
And here is some infos about table
league=# \d t_leagues
Table public.t_leagues
Column | Type |
Modifiers
+---+
id | integer | not null default
nextval('t_leagues_id_seq'::regclass)
name | character varying(20) | not null
data_start | date |
data_end | date |
awans | smallint | not null default 0
baraz | smallint | not null default 0
spadek | smallint | not null default 0
Indexes:
t_leagues_pkey PRIMARY KEY, btree (id)
Triggers:
league AFTER INSERT ON t_leagues FOR EACH STATEMENT EXECUTE PROCEDURE
add_view()
Re: [GENERAL] The tuple structure of a not-yet-assigned record is indeterminate.
M L wrote:
CREATE TRIGGER league AFTER insert ON t_leagues FOR STATEMENT EXECUTE
PROCEDURE add_view();
Then in psql I made an query and got error:
league=# INSERT INTO t_leagues (name) VALUES('3liga');
ERROR: record new is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function add_view line 4 at RAISE
`NEW' and `OLD' refer to the tuple operated on by this call of the
trigger. They're not valid for FOR EACH STATEMENT triggers, since the
statement might've added/modified/deleted zero or more than one tuple.
If you want to see the values of the tuples modified, use a FOR EACH ROW
trigger.
Whats wrong, I supposed that id is not reserverd at the moment
That's not the case. Your trigger is being called *AFTER* the row is
inserted, so the ID must've been assigned. In any case, default
expressions (including those used to assign values from sequences) are
actually evaluated even before the BEFORE triggers are invoked.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] bash postgres
Hi, I'm trying to pass variables on a bash script embedded with psql commands. cat header.txt to1,from1,subject1 to2,from2,subject2 to3,from3,subject3 to4,from4,subject4 cat b.sh #!/bin/bash two=2 psql -h localhost -U postgres -d mobile -c create temp table header ( field_1 textnot null, field_2 textnot null, field_3 textnot null ); \\copy header FROM header.txt CSV SELECT * FROM header limit $two; When I execute b.sh ERROR: syntax error at or near \ LINE 10: \copy header FROM header.txt CSV ^ How do I use \c (or any other psql commands beginning with a \) in a bash script? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] The tuple structure of a not-yet-assigned record is indeterminate.
2009/3/23 Craig Ringer cr...@postnewspapers.com.au
M L wrote:
CREATE TRIGGER league AFTER insert ON t_leagues FOR STATEMENT EXECUTE
PROCEDURE add_view();
Then in psql I made an query and got error:
league=# INSERT INTO t_leagues (name) VALUES('3liga');
ERROR: record new is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is
indeterminate.
CONTEXT: PL/pgSQL function add_view line 4 at RAISE
`NEW' and `OLD' refer to the tuple operated on by this call of the
trigger. They're not valid for FOR EACH STATEMENT triggers, since the
statement might've added/modified/deleted zero or more than one tuple.
If you want to see the values of the tuples modified, use a FOR EACH ROW
trigger.
I was trying varius trigers when I use:
league=# CREATE OR REPLACE FUNCTION add_view() RETURNS trigger AS $$
DECLARE
someint integer;
BEGIN
RAISE NOTICE 'dodajesz nowa lige %', NEW.id;
someint := NEW.id;
RAISE NOTICE 'dodajesz nowa lige %', someint;
CREATE VIEW tabelka AS SELECT someint FROM t_matches;
RETURN NULL;
END;
$$ language plpgsql;
CREATE TRIGGER league AFTER insert ON t_leagues FOR EACH ROW EXECUTE
PROCEDURE add_view();
I've got:
league=# INSERT INTO t_leagues (name) VALUES('3liga');
NOTICE: dodajesz nowa lige 31
NOTICE: dodajesz nowa lige 31
ERROR: there is no parameter $1
CONTEXT: SQL statement CREATE VIEW tabelka AS SELECT $1 FROM t_matches
PL/pgSQL function add_view line 7 at SQL statement
Any ideas?
Whats wrong, I supposed that id is not reserverd at the moment
That's not the case. Your trigger is being called *AFTER* the row is
inserted, so the ID must've been assigned. In any case, default
expressions (including those used to assign values from sequences) are
actually evaluated even before the BEFORE triggers are invoked.
That was my first thought, that it should be already assigned.
[GENERAL] Time intersect query
I'm wanting to optimize and improve a query to get the maximum number of users over a period of time. What I'm trying to accomplish is to get graphable data points of the maximum number of simultaneous users at a specified interval over a period of time, preferably with only a single pass through the data. -- streamlog table (I've only included relevant fields and indexes): id bigint ts timestamp viewtime integer client integer -- primary key on id field -- streamlog_tsrange_index btree (client, ts, startts(ts, viewtime)) \df+ startts List of functions Schema | Name | Result data type | Argument data types | Volatility | Owner | Language |Sou rce code | Description +-+-+--++---+--+--- -+- public | startts | timestamp without time zone | timestamp without time zone, integer | immutable | root | sql | select $1 - ($2::varch ar || ' seconds')::interval; | The ts timestamp is the time which the data was logged. The viewtime is the amount of time the user was online in seconds the startts function determines when the session started by subtracting viewtime from ts and returning a timestamp - My current procedure... 1) Create temporary table with each possible data point. This example uses recursive functions from pgsql 8.4 but was originally implemented by using large numbers of queries from php. My knowledge of the recursive functions is pretty week, but I was able to put this together without too much trouble. create temp table timerange as with recursive f as ( select '2009-03-21 18:20:00'::timestamp as a union all select a+'30 seconds'::interval as a from f where a '2009-03-21 20:20:00'::timestamp ) select a from f; 2) Update table with record counts alter table timerange add column mycount integer; explain analyze update timerange set mycount = (select count(*) from streamlogfoo where client = 3041 and a between startts(ts,viewtime) and ts); - This seems to work reasonably well, with the following exceptions... 1) The number reported is the number at the set time period, not the highest value between each data point. With a 30 second interval, this isn't a big problem, but with larger intervals gives results that do not match what I'm looking for (maximum users). 2) This does not scale well for large numbers of points, as internally each data point is a complete scan through the data, even though most of the data points will be common for the entire range. I'm thinking this would be a good use for the new window functions, but I'm not sure where to begin. Any ideas? -
Re: [GENERAL] bash postgres
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org]on Behalf Of Greenhorn Sent: Monday, 23 March 2009 3:03 PM To: pgsql-general@postgresql.org; pgsql-...@postgresql.org Subject: [GENERAL] bash postgres Hi, I'm trying to pass variables on a bash script embedded with psql commands. cat header.txt to1,from1,subject1 to2,from2,subject2 to3,from3,subject3 to4,from4,subject4 cat b.sh #!/bin/bash two=2 psql -h localhost -U postgres -d mobile -c create temp table header ( field_1 textnot null, field_2 textnot null, field_3 textnot null ); \\copy header FROM header.txt CSV SELECT * FROM header limit $two; When I execute b.sh ERROR: syntax error at or near \ LINE 10: \copy header FROM header.txt CSV ^ How do I use \c (or any other psql commands beginning with a \) in a bash script? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general #!/bin/bash two=2 Try something like psql -h localhost -U postgres -d mobile ENDOFSQL create temp table header ( field_1 textnot null, field_2 textnot null, field_3 textnot null ); \copy header FROM header.txt CSV SELECT * FROM header limit $two; ENDOFSQL The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore error - Any Idea?
Hi All, I am facing an error on executing the below command dump name: pg_dump_FcZ0.pnps_200903041201_1.2.1.0_base_testing databae name: pnqd_test $pg_restore -U postgres -p 5433 -d pnqd_test pg_dump_FcZ0.pnps_200903041201_1.2.1.0_base_testing pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3715; 0 0 ACL monitor postgres WARNING: errors ignored on restore: 1 I am not able to figure out this issue. Any idea guys. Thanks Deepak
