Обсуждение: Preventing a user to use implicit casts
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, Is there a way to define some implicit casts so that only one specific database user can use them, and they're not visible to others? I'm facing the case where to have same behaviour as in 8.2 for this "Non-character data types are no longer automatically cast to TEXT" 8.3 improvements, I used implicit casts proposed here: http://wiki.postgresql.org/images/d/d1/Pg83-implicit-casts.sql Problem yet is I can't use slony any more to replicate data, as it falls in error with this kind of messages: Jul 1 17:23:20 jck20 slon[6671]: [62-1] 2009-07-01 17:23:20 CEST FATAL cleanupThread: "select "_gvr".cleanupEvent('10 minutes'::interval, 'false'::boolean); " - ERROR: Jul 1 17:23:20 jck20 slon[6671]: [62-2] operator is not unique: unknown || integer Jul 1 17:23:20 jck20 slon[6671]: [62-3] LINE 1: SELECT 'PartInd_gvr_sl_log_' || $1 || '-node-' || $2 Jul 1 17:23:20 jck20 slon[6671]: [62-4] ^ Jul 1 17:23:20 jck20 slon[6671]: [62-5] HINT: Could not choose a best candidate operator. You might need to add explicit type casts. Jul 1 17:23:20 jck20 slon[6671]: [62-6] QUERY: SELECT 'PartInd_gvr_sl_log_' || $1 || '-node-' || $2 Jul 1 17:23:20 jck20 slon[6671]: [62-7] CONTEXT: PL/pgSQL function "addpartiallogindices" line 28 at assignment Jul 1 17:23:20 jck20 slon[6671]: [62-8] SQL statement "SELECT "_gvr".addPartialLogIndices()" Jul 1 17:23:20 jck20 slon[6671]: [62-9] PL/pgSQL function "logswitch_finish" line 99 at PERFORM I'm quit disappointed, as if I let slony replicate the application, still in a process of migration to 8.3 correct behaviour can't be used, on the other hand if I add implicit casts to allow application to work, I can't replicate with slony. Thanks in advance for any help. Regards, - -- Stéphane Schildknecht PostgreSQLFr - http://www.postgresql.fr -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFKS6OCA+REPKWGI0ERAqw1AJ9EPWbsDTp1K0glwBVW5+KCCbhcpgCggvh0 i8A4YmH3tg3lq7eK71IaaKI= =wKwd -----END PGP SIGNATURE-----
On Wed, Jul 1, 2009 at 11:57 AM, "Stéphane A. Schildknecht"<stephane.schildknecht@postgresqlfr.org> wrote: > Problem yet is I can't use slony any more to replicate data, as it falls in > I'm quit disappointed, as if I let slony replicate the application, still in a > process of migration to 8.3 correct behaviour can't be used, on the other hand > if I add implicit casts to allow application to work, I can't replicate with slony. So, is fixing the app an option?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Scott Marlowe a écrit : > On Wed, Jul 1, 2009 at 11:57 AM, "Stéphane A. > Schildknecht"<stephane.schildknecht@postgresqlfr.org> wrote: >> Problem yet is I can't use slony any more to replicate data, as it falls in > >> I'm quit disappointed, as if I let slony replicate the application, still in a >> process of migration to 8.3 correct behaviour can't be used, on the other hand >> if I add implicit casts to allow application to work, I can't replicate with slony. > > So, is fixing the app an option? > Definitely not... as it would last too long to be an option. It surely is a long-term option. And it needs to be done, but not yet. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFKS6a+A+REPKWGI0ERArdRAKCfsj/VCuY7zRNFVGkezW+TrUq0XwCgmD8Y NCVJ3DWCT0PSGHtWGcUXg04= =maRT -----END PGP SIGNATURE-----
2009/7/1 "Stéphane A. Schildknecht" <stephane.schildknecht@postgresqlfr.org>: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi, > > Is there a way to define some implicit casts so that only one specific database > user can use them, and they're not visible to others? no - it's not possible - maybe with some very ugly hacks - you need to hack cache. regards Pavel Stehule > > I'm facing the case where to have same behaviour as in 8.2 for this > "Non-character data types are no longer automatically cast to TEXT" 8.3 > improvements, I used implicit casts proposed here: > > http://wiki.postgresql.org/images/d/d1/Pg83-implicit-casts.sql > > Problem yet is I can't use slony any more to replicate data, as it falls in > error with this kind of messages: > > Jul 1 17:23:20 jck20 slon[6671]: [62-1] 2009-07-01 17:23:20 CEST FATAL > cleanupThread: "select "_gvr".cleanupEvent('10 minutes'::interval, > 'false'::boolean); " - ERROR: > Jul 1 17:23:20 jck20 slon[6671]: [62-2] operator is not unique: unknown || > integer > Jul 1 17:23:20 jck20 slon[6671]: [62-3] LINE 1: SELECT 'PartInd_gvr_sl_log_' > || $1 || '-node-' || $2 > Jul 1 17:23:20 jck20 slon[6671]: [62-4] ^ > Jul 1 17:23:20 jck20 slon[6671]: [62-5] HINT: Could not choose a best > candidate operator. You might need to add explicit type casts. > Jul 1 17:23:20 jck20 slon[6671]: [62-6] QUERY: SELECT 'PartInd_gvr_sl_log_' > || $1 || '-node-' || $2 > Jul 1 17:23:20 jck20 slon[6671]: [62-7] CONTEXT: PL/pgSQL function > "addpartiallogindices" line 28 at assignment > Jul 1 17:23:20 jck20 slon[6671]: [62-8] SQL statement "SELECT > "_gvr".addPartialLogIndices()" > Jul 1 17:23:20 jck20 slon[6671]: [62-9] PL/pgSQL function "logswitch_finish" > line 99 at PERFORM > > I'm quit disappointed, as if I let slony replicate the application, still in a > process of migration to 8.3 correct behaviour can't be used, on the other hand > if I add implicit casts to allow application to work, I can't replicate with slony. > > Thanks in advance for any help. > > Regards, > - -- > Stéphane Schildknecht > PostgreSQLFr - http://www.postgresql.fr > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFKS6OCA+REPKWGI0ERAqw1AJ9EPWbsDTp1K0glwBVW5+KCCbhcpgCggvh0 > i8A4YmH3tg3lq7eK71IaaKI= > =wKwd > -----END PGP SIGNATURE----- > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Wed, Jul 1, 2009 at 12:11 PM, "Stéphane A. Schildknecht"<stephane.schildknecht@postgresqlfr.org> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Scott Marlowe a écrit : >> On Wed, Jul 1, 2009 at 11:57 AM, "Stéphane A. >> Schildknecht"<stephane.schildknecht@postgresqlfr.org> wrote: >>> Problem yet is I can't use slony any more to replicate data, as it falls in >> >>> I'm quit disappointed, as if I let slony replicate the application, still in a >>> process of migration to 8.3 correct behaviour can't be used, on the other hand >>> if I add implicit casts to allow application to work, I can't replicate with slony. >> >> So, is fixing the app an option? >> > > Definitely not... as it would last too long to be an option. > It surely is a long-term option. And it needs to be done, but not yet. So, can you keep up x numbers of slony reps running 8.2 that only the app can see, replicate to an 8.3 that doesn't get hit by the user, then switch to the new 8.3 instance(s) and apply the gruesome auto-cast to the 8.3 afterwards, and then slony will work between two patched 8.3 instances? Just a SWAG, I'm not there to test it or anything.