proof concept - access to session variables on client side
Hello
I worked on simple patch, that enable access from server side to
client side data. It add two new hooks to libpq - one for returning of
local context, second for setting of local context.
A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.
pavel ~/src/postgresql/src $ cat test.sql
\echo value of external paremeter is :"myvar"
do $$
begin
-- we can take any session variable on client side
-- it is safe against to SQL injection
raise notice 'external parameter accessed from plpgsql is "%"',
hgetvar('myvar');
-- we can change this session variable and finish transaction
perform hsetvar('myvar', 'Hello, World');
end;
$$ language plpgsql;
\echo new value of session variable is :"myvar"
cat test.sql | psql postgres -v myvar=Hello
value of external paremeter is "Hello"
NOTICE: external parameter accessed from plpgsql is "Hello"
DO
new value of session variable is "Hello, World"
This is just proof concept - there should be better integration with
pl languages, using cache for read on server side, ...
Notices?
Regards
Pavel Stehule
Attachments:
client-session-vars.diffapplication/octet-stream; name=client-session-vars.diffDownload
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index c5b0a75..a3abb56 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -19,7 +19,7 @@ OBJS = acl.o arrayfuncs.o array_selfuncs.o array_typanalyze.o \
array_userfuncs.o arrayutils.o bool.o \
cash.o char.o date.o datetime.o datum.o domains.o \
enum.o float.o format_type.o \
- geo_ops.o geo_selfuncs.o int.o int8.o json.o like.o lockfuncs.o \
+ geo_ops.o geo_selfuncs.o hostvars.o int.o int8.o json.o like.o lockfuncs.o \
misc.o nabstime.o name.o numeric.o numutils.o \
oid.o oracle_compat.o pseudotypes.o rangetypes.o rangetypes_gist.o \
rowtypes.o regexp.o regproc.o ruleutils.o selfuncs.o \
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 9a6306b..65f3569 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -77,6 +77,24 @@ static void process_psqlrc_file(char *filename);
static void showVersion(void);
static void EstablishVariableSpace(void);
+static void
+hostvarReceiver(const char *varname, const char *value)
+{
+ SetVariable(pset.vars, varname, value);
+}
+
+static void
+hostvarSender(const char *varname, const char **value)
+{
+ char *content;
+
+ content = GetVariable(pset.vars, varname);
+ if (content == NULL)
+ *value = "";
+ else
+ *value = content;
+}
+
/*
*
* main
@@ -230,6 +248,9 @@ main(int argc, char *argv[])
PQsetNoticeProcessor(pset.db, NoticeProcessor, NULL);
+ PQsetHostvarReceiver(pset.db, hostvarReceiver);
+ PQsetHostvarSender(pset.db, hostvarSender);
+
SyncVariables();
if (options.action == ACT_LIST_DB)
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index bee7154..cc43e39 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4646,6 +4646,11 @@ DATA(insert OID = 4031 ( spg_text_leaf_consistent PGNSP PGUID 12 1 0 0 0 f f f
DESCR("SP-GiST support for suffix tree over text");
+DATA(insert OID = 3920 ( hgetvar PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 25 "25" _null_ _null_ _null_ _null_ get_hostvar_text _null_ _null_ _null_ ));
+DATA(insert OID = 3921 ( hsetvar PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 2278 "25 25" _null_ _null_ _null_ _null_ set_hostvar_text _null_ _null_ _null_ ));
+
+
+
/*
* Symbolic values for provolatile column: these indicate whether the result
* of a function is dependent *only* on the values of its explicit arguments,
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 1063403..c79a2b4 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -1142,4 +1142,8 @@ extern Datum pg_prepared_statement(PG_FUNCTION_ARGS);
/* utils/mmgr/portalmem.c */
extern Datum pg_cursor(PG_FUNCTION_ARGS);
+/* adt/hostvars.c */
+extern Datum get_hostvar_text(PG_FUNCTION_ARGS);
+extern Datum set_hostvar_text(PG_FUNCTION_ARGS);
+
#endif /* BUILTINS_H */
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
index 1251455..a0009c0 100644
--- a/src/interfaces/libpq/exports.txt
+++ b/src/interfaces/libpq/exports.txt
@@ -163,3 +163,5 @@ PQlibVersion 160
PQsetRowProcessor 161
PQgetRowProcessor 162
PQskipResult 163
+PQsetHostvarReceiver 164
+PQsetHostvarSender 165
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 7c9fa34..143d641 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -2714,6 +2714,9 @@ makeEmptyPGconn(void)
conn->noticeHooks.noticeRec = defaultNoticeReceiver;
conn->noticeHooks.noticeProc = defaultNoticeProcessor;
+ conn->hostvarReceiver = NULL;
+ conn->hostvarSender = NULL;
+
conn->status = CONNECTION_BAD;
conn->asyncStatus = PGASYNC_IDLE;
conn->xactStatus = PQTRANS_IDLE;
@@ -5383,6 +5386,27 @@ PQsetNoticeProcessor(PGconn *conn, PQnoticeProcessor proc, void *arg)
return old;
}
+void
+PQsetHostvarReceiver(PGconn *conn, PQhostvarReceiver proc)
+{
+ if (conn == NULL)
+ return;
+
+ if (proc)
+ conn->hostvarReceiver = proc;
+}
+
+void
+PQsetHostvarSender(PGconn *conn, PQhostvarSender proc)
+{
+ if (conn == NULL)
+ return;
+
+ if (proc)
+ conn->hostvarSender = proc;
+}
+
+
/*
* The default notice message receiver just gets the standard notice text
* and sends it to the notice processor. This two-level setup exists
diff --git a/src/interfaces/libpq/fe-protocol3.c b/src/interfaces/libpq/fe-protocol3.c
index 173af2e..f49d353 100644
--- a/src/interfaces/libpq/fe-protocol3.c
+++ b/src/interfaces/libpq/fe-protocol3.c
@@ -148,7 +148,61 @@ pqParseInput3(PGconn *conn)
* from config file due to SIGHUP), but otherwise we hold off until
* BUSY state.
*/
- if (id == 'A')
+
+ if (id == 'v')
+ {
+ PQExpBufferData varnameBuf, valueBuf;
+ char result;
+ char *var;
+
+ initPQExpBuffer(&varnameBuf);
+ initPQExpBuffer(&valueBuf);
+
+ if (pqGetc(&result, conn))
+ return;
+
+ if (result == 1)
+ {
+ if (pqGets(&varnameBuf, conn))
+ return;
+
+ if (conn->hostvarSender != NULL)
+ (*conn->hostvarSender) (varnameBuf.data, &var);
+
+ if (pqPutMsgStart('v', false, conn) < 0 ||
+ pqPuts(var, conn) < 0 ||
+ pqPutMsgEnd(conn) < 0 ||
+ pqFlush(conn) < 0)
+ {
+
+ pqHandleSendFailure(conn);
+ return;
+ }
+
+ resetPQExpBuffer(&varnameBuf);
+ }
+ else if (result == 2)
+ {
+ if (pqGets(&varnameBuf, conn))
+ return;
+ if (pqGets(&valueBuf, conn))
+ return;
+
+ if (conn->hostvarReceiver != NULL)
+ (*conn->hostvarReceiver) (varnameBuf.data, valueBuf.data);
+
+ resetPQExpBuffer(&varnameBuf);
+ resetPQExpBuffer(&valueBuf);
+ }
+
+ if (pqGetc(&result, conn))
+ return;
+
+ if (result != '\0')
+ return;
+
+ }
+ else if (id == 'A')
{
if (getNotify(conn))
return;
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
index 67db611..1c8ba3b 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -169,6 +169,11 @@ typedef int (*PQrowProcessor) (PGresult *res, const PGdataValue *columns,
typedef void (*PQnoticeReceiver) (void *arg, const PGresult *res);
typedef void (*PQnoticeProcessor) (void *arg, const char *message);
+/* Function types for hostvar handling callbacks */
+typedef void (*PQhostvarReceiver) (const char *varname, const char *value);
+typedef void (*PQhostvarSender) (const char *varname, const char **value);
+
+
/* Print options for PQprint() */
typedef char pqbool;
@@ -349,6 +354,10 @@ extern PQnoticeProcessor PQsetNoticeProcessor(PGconn *conn,
PQnoticeProcessor proc,
void *arg);
+extern void PQsetHostvarReceiver(PGconn *conn, PQhostvarReceiver proc);
+extern void PQsetHostvarSender(PGconn *conn, PQhostvarSender proc);
+
+
/*
* Used to set callback that prevents concurrent access to
* non-thread safe functions that libpq needs.
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 4bc8926..0ed4761 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -157,6 +157,7 @@ typedef struct
void *noticeProcArg;
} PGNoticeHooks;
+
typedef struct PGEvent
{
PGEventProc proc; /* the function to call on events */
@@ -336,6 +337,10 @@ struct pg_conn
int nEvents; /* number of active events */
int eventArraySize; /* allocated array size */
+ /* hostvar processing */
+ PQhostvarReceiver hostvarReceiver;
+ PQhostvarSender hostvarSender;
+
/* Status indicators */
ConnStatusType status;
PGAsyncStatusType asyncStatus;
diff --git a/src/test.sql b/src/test.sql
new file mode 100644
index 0000000..c3764ba
--- /dev/null
+++ b/src/test.sql
@@ -0,0 +1,14 @@
+\echo value of external paremeter is :"myvar"
+
+do $$
+begin
+ -- we can take any session variable on client side
+ -- it is safe against to SQL injection
+ raise notice 'external parameter accessed from plpgsql is "%"', hgetvar('myvar');
+
+ -- we can change this session variable and finish transaction
+ perform hsetvar('myvar', 'Hello, World');
+end;
+$$ language plpgsql;
+
+\echo new value of session variable is :"myvar"
On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
I worked on simple patch, that enable access from server side to
client side data. It add two new hooks to libpq - one for returning of
local context, second for setting of local context.A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.pavel ~/src/postgresql/src $ cat test.sql
\echo value of external paremeter is :"myvar"do $$
begin
-- we can take any session variable on client side
-- it is safe against to SQL injection
raise notice 'external parameter accessed from plpgsql is "%"',
hgetvar('myvar');-- we can change this session variable and finish transaction
perform hsetvar('myvar', 'Hello, World');
end;
$$ language plpgsql;\echo new value of session variable is :"myvar"
cat test.sql | psql postgres -v myvar=Hello
value of external paremeter is "Hello"
NOTICE: external parameter accessed from plpgsql is "Hello"
DO
new value of session variable is "Hello, World"This is just proof concept - there should be better integration with
pl languages, using cache for read on server side, ...Notices?
Why not just use a custom GUC variable instead? E.g. you could have
psql SET "psql.myvar='Hello, World'", and then you'd need no changes
at all in the backend? Maybe have a "shorthand interface" for
accessing GUCs in psql would help in making it easier, but do we
really need a whole new variable concept?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
2012/6/26 Magnus Hagander <magnus@hagander.net>:
On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
I worked on simple patch, that enable access from server side to
client side data. It add two new hooks to libpq - one for returning of
local context, second for setting of local context.A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.pavel ~/src/postgresql/src $ cat test.sql
\echo value of external paremeter is :"myvar"do $$
begin
-- we can take any session variable on client side
-- it is safe against to SQL injection
raise notice 'external parameter accessed from plpgsql is "%"',
hgetvar('myvar');-- we can change this session variable and finish transaction
perform hsetvar('myvar', 'Hello, World');
end;
$$ language plpgsql;\echo new value of session variable is :"myvar"
cat test.sql | psql postgres -v myvar=Hello
value of external paremeter is "Hello"
NOTICE: external parameter accessed from plpgsql is "Hello"
DO
new value of session variable is "Hello, World"This is just proof concept - there should be better integration with
pl languages, using cache for read on server side, ...Notices?
Why not just use a custom GUC variable instead? E.g. you could have
psql SET "psql.myvar='Hello, World'", and then you'd need no changes
at all in the backend? Maybe have a "shorthand interface" for
accessing GUCs in psql would help in making it easier, but do we
really need a whole new variable concept?
GUC variables doesn't help with access to psql's command line
parameters from DO PL code.
Regards
Pavel
Show quoted text
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2012/6/26 Magnus Hagander <magnus@hagander.net>:
On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
I worked on simple patch, that enable access from server side to
client side data. It add two new hooks to libpq - one for returning of
local context, second for setting of local context.A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.pavel ~/src/postgresql/src $ cat test.sql
\echo value of external paremeter is :"myvar"do $$
begin
-- we can take any session variable on client side
-- it is safe against to SQL injection
raise notice 'external parameter accessed from plpgsql is "%"',
hgetvar('myvar');-- we can change this session variable and finish transaction
perform hsetvar('myvar', 'Hello, World');
end;
$$ language plpgsql;\echo new value of session variable is :"myvar"
cat test.sql | psql postgres -v myvar=Hello
value of external paremeter is "Hello"
NOTICE: external parameter accessed from plpgsql is "Hello"
DO
new value of session variable is "Hello, World"This is just proof concept - there should be better integration with
pl languages, using cache for read on server side, ...Notices?
Why not just use a custom GUC variable instead? E.g. you could have
psql SET "psql.myvar='Hello, World'", and then you'd need no changes
at all in the backend? Maybe have a "shorthand interface" for
accessing GUCs in psql would help in making it easier, but do we
really need a whole new variable concept?GUC variables doesn't help with access to psql's command line
parameters from DO PL code.
But with a small change to psql they could, without the need for a
whole new type of variable. For example, psql could set all those
variable as "psql.<commandlinevarname>", which could then be accessed
from the DO PL code just fine.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
2012/6/26 Magnus Hagander <magnus@hagander.net>:
On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2012/6/26 Magnus Hagander <magnus@hagander.net>:
On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
I worked on simple patch, that enable access from server side to
client side data. It add two new hooks to libpq - one for returning of
local context, second for setting of local context.A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.pavel ~/src/postgresql/src $ cat test.sql
\echo value of external paremeter is :"myvar"do $$
begin
-- we can take any session variable on client side
-- it is safe against to SQL injection
raise notice 'external parameter accessed from plpgsql is "%"',
hgetvar('myvar');-- we can change this session variable and finish transaction
perform hsetvar('myvar', 'Hello, World');
end;
$$ language plpgsql;\echo new value of session variable is :"myvar"
cat test.sql | psql postgres -v myvar=Hello
value of external paremeter is "Hello"
NOTICE: external parameter accessed from plpgsql is "Hello"
DO
new value of session variable is "Hello, World"This is just proof concept - there should be better integration with
pl languages, using cache for read on server side, ...Notices?
Why not just use a custom GUC variable instead? E.g. you could have
psql SET "psql.myvar='Hello, World'", and then you'd need no changes
at all in the backend? Maybe have a "shorthand interface" for
accessing GUCs in psql would help in making it easier, but do we
really need a whole new variable concept?GUC variables doesn't help with access to psql's command line
parameters from DO PL code.But with a small change to psql they could, without the need for a
whole new type of variable. For example, psql could set all those
variable as "psql.<commandlinevarname>", which could then be accessed
from the DO PL code just fine.
yes, it is possibility too. It has different issues - it can send
unwanted variables - maybe some compromise is optimum.
Show quoted text
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Tue, Jun 26, 2012 at 10:12:52AM +0200, Pavel Stehule wrote:
2012/6/26 Magnus Hagander <magnus@hagander.net>:
On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2012/6/26 Magnus Hagander <magnus@hagander.net>:
On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
I worked on simple patch, that enable access from server side to
client side data. It add two new hooks to libpq - one for returning of
local context, second for setting of local context.A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.pavel ~/src/postgresql/src $ cat test.sql
\echo value of external paremeter is :"myvar"do $$
begin
-- we can take any session variable on client side
-- it is safe against to SQL injection
raise notice 'external parameter accessed from plpgsql is "%"',
hgetvar('myvar');-- we can change this session variable and finish transaction
perform hsetvar('myvar', 'Hello, World');
end;
$$ language plpgsql;\echo new value of session variable is :"myvar"
cat test.sql | psql postgres -v myvar=Hello
value of external paremeter is "Hello"
NOTICE: external parameter accessed from plpgsql is "Hello"
DO
new value of session variable is "Hello, World"This is just proof concept - there should be better integration with
pl languages, using cache for read on server side, ...Notices?
Why not just use a custom GUC variable instead? E.g. you could have
psql SET "psql.myvar='Hello, World'", and then you'd need no changes
at all in the backend? Maybe have a "shorthand interface" for
accessing GUCs in psql would help in making it easier, but do we
really need a whole new variable concept?GUC variables doesn't help with access to psql's command line
parameters from DO PL code.But with a small change to psql they could, without the need for a
whole new type of variable. For example, psql could set all those
variable as "psql.<commandlinevarname>", which could then be accessed
from the DO PL code just fine.yes, it is possibility too. It has different issues - it can send
unwanted variables -
Could you expand on this just a bit? Are you picturing something an
attacker could somehow use, or...?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
2012/6/26 David Fetter <david@fetter.org>:
On Tue, Jun 26, 2012 at 10:12:52AM +0200, Pavel Stehule wrote:
2012/6/26 Magnus Hagander <magnus@hagander.net>:
On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
2012/6/26 Magnus Hagander <magnus@hagander.net>:
On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hello
I worked on simple patch, that enable access from server side to
client side data. It add two new hooks to libpq - one for returning of
local context, second for setting of local context.A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.pavel ~/src/postgresql/src $ cat test.sql
\echo value of external paremeter is :"myvar"do $$
begin
-- we can take any session variable on client side
-- it is safe against to SQL injection
raise notice 'external parameter accessed from plpgsql is "%"',
hgetvar('myvar');-- we can change this session variable and finish transaction
perform hsetvar('myvar', 'Hello, World');
end;
$$ language plpgsql;\echo new value of session variable is :"myvar"
cat test.sql | psql postgres -v myvar=Hello
value of external paremeter is "Hello"
NOTICE: external parameter accessed from plpgsql is "Hello"
DO
new value of session variable is "Hello, World"This is just proof concept - there should be better integration with
pl languages, using cache for read on server side, ...Notices?
Why not just use a custom GUC variable instead? E.g. you could have
psql SET "psql.myvar='Hello, World'", and then you'd need no changes
at all in the backend? Maybe have a "shorthand interface" for
accessing GUCs in psql would help in making it easier, but do we
really need a whole new variable concept?GUC variables doesn't help with access to psql's command line
parameters from DO PL code.But with a small change to psql they could, without the need for a
whole new type of variable. For example, psql could set all those
variable as "psql.<commandlinevarname>", which could then be accessed
from the DO PL code just fine.yes, it is possibility too. It has different issues - it can send
unwanted variables -Could you expand on this just a bit? Are you picturing something an
attacker could somehow use, or...?
it is not security issue - just I dislike sending complete stack, when
just only one variable should be used.
Regards
Pavel
Show quoted text
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.icsRemember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jun 26, 2012 at 3:05 AM, Magnus Hagander <magnus@hagander.net> wrote:
But with a small change to psql they could, without the need for a
whole new type of variable. For example, psql could set all those
variable as "psql.<commandlinevarname>", which could then be accessed
from the DO PL code just fine.
That's a really neat idea.
merlin
2012/6/26 Merlin Moncure <mmoncure@gmail.com>:
On Tue, Jun 26, 2012 at 3:05 AM, Magnus Hagander <magnus@hagander.net> wrote:
But with a small change to psql they could, without the need for a
whole new type of variable. For example, psql could set all those
variable as "psql.<commandlinevarname>", which could then be accessed
from the DO PL code just fine.That's a really neat idea.
yes, it can be good idea - psql sends some status variables on start,
so it should be small patch
Pavel
Show quoted text
merlin
Pavel Stehule <pavel.stehule@gmail.com> writes:
it is not security issue - just I dislike sending complete stack, when
just only one variable should be used.
That's a pretty darn weak argument. If I read the patch correctly, what
you're proposing involves a dynamic fetch from the client at runtime,
which is going to be disastrous for performance. Quite aside from the
network round trip involved, the fetch function would have to be marked
volatile (since it has client-visible side-effects, not to mention that
we don't know when the client might change the variable value); which
would really hurt any query involving it, and probably lead to yet more
round trips.
Pushing over the known values once at session start (and individual
values after updates) is likely to be vastly better-performant than
this. Matters could be improved further by requiring variables to be
sent to the server to be explicitly marked, which seems like a good
idea anyway in case anybody has security concerns that they're not
going to let you airily dismiss.
Another thing I don't care for is the unannounced protocol extension.
This feature is just not interesting enough to justify breaking
client compatibility, but that's what it would do as proposed.
Clients that haven't heard of this 'v' message would probably
think they'd lost sync and drop the connection.
(BTW, the patch doesn't seem to include the added backend source file?)
regards, tom lane
Merlin Moncure <mmoncure@gmail.com> writes:
On Tue, Jun 26, 2012 at 3:05 AM, Magnus Hagander <magnus@hagander.net> wrote:
But with a small change to psql they could, without the need for a
whole new type of variable. For example, psql could set all those
variable as "psql.<commandlinevarname>", which could then be accessed
from the DO PL code just fine.
That's a really neat idea.
I do see a problem with this client-push idea, which is what happens if
psql sends a SET and later the active transaction gets rolled back.
psql does not have enough knowledge to be sure whether it lost the SET
or not. It could hack things by always resending all variables after
any rollback, but ugh.
We could address that by inventing a non-transactional variant of SET,
perhaps. Not sure it's worth the complication though --- I don't think
I want to have to define how that would interact with other variants
of SET in the same transaction ...
Another approach would be to define such variables as being truly
shared, in the spirit of last-update-wins multi master replication.
The backend sends over its values using the existing GUC_REPORT
mechanism. So a rollback would cause the psql-side variable to revert
as well. Not actually sure if that behavior would be more or less
useful than a simpler definition, but it's worth thinking about.
In this connection, there was some recent discussion in the jdbc list
of wanting clients to be able to set the GUC_REPORT flag on any GUC
variable, because the jdbc driver would like to track some settings we
have not seen fit to mark that way. Not sure if anybody mentioned that
on -hackers yet, but it's coming. If we had that ability then a
shared-variable behavior like this could be built entirely on the psql
side: the push part is just SET, and the pull part is GUC_REPORT.
regards, tom lane
2012/6/26 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
it is not security issue - just I dislike sending complete stack, when
just only one variable should be used.That's a pretty darn weak argument. If I read the patch correctly, what
you're proposing involves a dynamic fetch from the client at runtime,
which is going to be disastrous for performance. Quite aside from the
network round trip involved, the fetch function would have to be marked
volatile (since it has client-visible side-effects, not to mention that
we don't know when the client might change the variable value); which
would really hurt any query involving it, and probably lead to yet more
round trips.
I didn't implement any optimization, because it is just concept, but
server side caching is possible. Then only "first read" and any
"write" can do some network communication.
Pushing over the known values once at session start (and individual
values after updates) is likely to be vastly better-performant than
this. Matters could be improved further by requiring variables to be
sent to the server to be explicitly marked, which seems like a good
idea anyway in case anybody has security concerns that they're not
going to let you airily dismiss.
this is decision between push and pull model. Both variants has own
issues and benefits. Probably pull model has more complex changes in
protocol implementation. Push model needs more code on client side.
Propagation psql variables should be enabled some command line option
and can be disabled by default.
Another thing I don't care for is the unannounced protocol extension.
This feature is just not interesting enough to justify breaking
client compatibility, but that's what it would do as proposed.
Clients that haven't heard of this 'v' message would probably
think they'd lost sync and drop the connection.
yes, it needs protocol extension and increasing version too. But I
don't afraid about dissynchronisation - server doesn't send 'v'
message when client doesn't support it.
(BTW, the patch doesn't seem to include the added backend source file?)
The goal of this patch is showing requested functionality and checking
how hard is implementation
Regards
Pavel
Show quoted text
regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> writes:
Another thing I don't care for is the unannounced protocol extension.
yes, it needs protocol extension and increasing version too. But I
don't afraid about dissynchronisation - server doesn't send 'v'
message when client doesn't support it.
And you would know that how, exactly?
regards, tom lane
2012/6/27 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
Another thing I don't care for is the unannounced protocol extension.
yes, it needs protocol extension and increasing version too. But I
don't afraid about dissynchronisation - server doesn't send 'v'
message when client doesn't support it.And you would know that how, exactly?
minor version of protocol can be used
http://archives.postgresql.org/pgsql-hackers/2011-12/msg00025.php
I don't know if this topic is done, I only remember this thread
Regards
Pavel Stehule
Show quoted text
regards, tom lane
On tis, 2012-06-26 at 07:06 +0200, Pavel Stehule wrote:
A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.
I think it would be better if DO could be extended into some kind of
"lambda", taking parameters and returning a value. Then you can use
existing infrastructure for passing values and saving the return. It
would also extend better to other languages.
2012/7/3 Peter Eisentraut <peter_e@gmx.net>:
On tis, 2012-06-26 at 07:06 +0200, Pavel Stehule wrote:
A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.I think it would be better if DO could be extended into some kind of
"lambda", taking parameters and returning a value. Then you can use
existing infrastructure for passing values and saving the return. It
would also extend better to other languages.
I did it
http://archives.postgresql.org/pgsql-hackers/2010-07/msg00118.php
it is other approach. I think so callback from server to client is
more general solution - access to client system variables is possible,
but I know so this is very obscure and risk idea.
but any form of parametrization of PL block can be nice.
Regards
Pavel
Show quoted text
Le 3 juil. 2012 à 19:53, Peter Eisentraut <peter_e@gmx.net> a écrit :
I think it would be better if DO could be extended into some kind of
"lambda", taking parameters and returning a value. Then you can use
existing infrastructure for passing values and saving the return. It
would also extend better to other languages.
+1
--
dim