[GENERAL] Temporarily suspend a user account?
Hello, Is there a way to temporarily suspend a user account? I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work. We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect. Has anyone else solved this issue? Thank you! -Felipe Gasper Houston, TX -- 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] Temporarily suspend a user account?
Possibly, To disble: ALTER USER name RENAME TO xname; To enable ALTER USER xname RENAME TO name; ??? On Fri, Feb 6, 2015 at 3:57 PM, Felipe Gasper fel...@felipegasper.com wrote: Hello, Is there a way to temporarily suspend a user account? I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work. We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect. Has anyone else solved this issue? Thank you! -Felipe Gasper Houston, TX -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Temporarily suspend a user account?
I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. Melvin Davidson-5 wrote Possibly, To disble: ALTER USER name RENAME TO xname; To enable ALTER USER xname RENAME TO name; Given that removing login privileges is a no-go this doesn't seem like an acceptable solution for the OP. Its unclear exactly what catalog data is being used but likely the role name is an important one. David J. -- View this message in context: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5836987.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] Temporarily suspend a user account?
Felipe Gasper wrote Hello, Is there a way to temporarily suspend a user account? I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work. We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect. Has anyone else solved this issue? Thank you! Personally untested: ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in the past http://www.postgresql.org/docs/9.4/interactive/sql-alterrole.html David J. -- View this message in context: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5836982.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] Temporarily suspend a user account?
Might not do what you want, but I just change the password. -- Mike Nolan On Fri, Feb 6, 2015 at 4:11 PM, Melvin Davidson melvin6...@gmail.com wrote: Possibly, To disble: ALTER USER name RENAME TO xname; To enable ALTER USER xname RENAME TO name; ??? On Fri, Feb 6, 2015 at 3:57 PM, Felipe Gasper fel...@felipegasper.com wrote: Hello, Is there a way to temporarily suspend a user account? I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work. We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect. Has anyone else solved this issue? Thank you! -Felipe Gasper Houston, TX -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
[GENERAL] A Core Team statement on recent conference events
Community members: The PostgreSQL user group in Moscow is currently conducting their first-ever PostgreSQL-themed conference, which has been a tremendous success. Unfortunately, the venue booked by the conference chose to include inappropriate dancers as part of their entertainment package. The conference organizers and the Russian PostgreSQL community were not aware of the nature of the entertainment supplied ahead of time. The PostgreSQL Core Team believes there is no place for inappropriate or discriminatory behaviour at PostgreSQL conferences and try to ensure that all our conferences are suitable for anyone to attend. As PostgreSQL is an Open Source project with volunteer contributors and a federated organizational structure, we do not have supervisory control over how individual conferences are organized, which means that sometimes they do not benefit from general community experience. The Russian conference organizers are expected to comment on this unforseen incident once the conference is concluded. The international community will be working with them to make sure that this mistake is not repeated. -- Josh Berkus On behalf of the PostgreSQL Core Team -- 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] Temporarily suspend a user account?
On 2/6/15, David G Johnston david.g.johns...@gmail.com wrote: On Fri, Feb 6, 2015 at 2:22 PM, Michael Nolan [via PostgreSQL] ml-node+s1045698n5836989...@n5.nabble.com wrote: Might not do what you want, but I just change the password. How do you do that and re-enable using the previous password? David J. Encrypted passwords are kept in the pg_shadow file and should start with 'md5'. Just save a copy of the encrypted password for that user and when you want to re-enable that user do: alter user xxx encrypted password 'md5'; I have tested this on 9.3.5. -- Mike Nolan -- 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] Temporarily suspend a user account?
On 6 Feb 2015 4:21 PM, Jerry Sievers wrote: David G Johnston david.g.johns...@gmail.com writes: On Fri, Feb 6, 2015 at 2:29 PM, Felipe Gasper [via PostgreSQL] [hidden email] wrote: On 6 Feb 2015 3:15 PM, David G Johnston wrote: Felipe Gasper wrote Hello, Is there a way to temporarily suspend a user account? I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work. We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect. Has anyone else solved this issue? Thank you! Personally untested: ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in the past This doesn’t work, either, because it will clobber any custom expiration time for the role … -FG ​Since everything about a role can be customized, and there is no simple enabled boolean, you need to take a known value, cache it somewhere, make your change, then restore the cached value; or just edit pg_hba.conf and add reject entries for the role in question. Here we go... disable: update pg_authid set rolpassword = rolpassword || '.disabled' where rolname = 'foo'; enable: update pg_authid set rolpassword = rtrim(rolpassword, 'disabled') where rolname = 'foo'; This does appear to work. It didn’t work earlier when I mangled the format such that it no longer began with “md5”, though. Weird. Anyway, thank you! :) -FG -- 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] Temporarily suspend a user account?
On 6 Feb 2015 3:24 PM, David G Johnston wrote: On Fri, Feb 6, 2015 at 2:22 PM, Michael Nolan [via PostgreSQL] [hidden email] /user/SendEmail.jtp?type=nodenode=5836990i=0wrote: Might not do what you want, but I just change the password. How do you do that and re-enable using the previous password? Is there no way to “sync up” from a custom MD5 hash in pg_authid? -FG -- 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] Temporarily suspend a user account?
David G Johnston david.g.johns...@gmail.com writes: On Fri, Feb 6, 2015 at 2:29 PM, Felipe Gasper [via PostgreSQL] [hidden email] wrote: On 6 Feb 2015 3:15 PM, David G Johnston wrote: Felipe Gasper wrote Hello, Is there a way to temporarily suspend a user account? I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesnât work. We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesnât seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect. Has anyone else solved this issue? Thank you! Personally untested: ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in the past This doesnât work, either, because it will clobber any custom expiration time for the role ⦠-FG âSince everything about a role can be customized, and there is no simple enabled boolean, you need to take a known value, cache it somewhere, make your change, then restore the cached value; or just edit pg_hba.conf and add reject entries for the role in question. Here we go... disable: update pg_authid set rolpassword = rolpassword || '.disabled' where rolname = 'foo'; enable: update pg_authid set rolpassword = rtrim(rolpassword, 'disabled') where rolname = 'foo'; David J. â  -- View this message in context: Re: Temporarily suspend a user account? Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Temporarily suspend a user account?
Melvin Davidson melvin6...@gmail.com writes: Possibly, To disble: ALTER USER name RENAME TO xname; Fine if you don't care about losing password :-) yomamadb/postgres =# create user foo password 'foowow'; CREATE ROLE yomamadb/postgres =# alter user foo rename to fooxxx; NOTICE: MD5 password cleared because of role rename --- ALTER ROLE yomamadb/postgres =# To enable ALTER USER xname RENAME TO name; ??? On Fri, Feb 6, 2015 at 3:57 PM, Felipe Gasper fel...@felipegasper.com wrote: Hello,     Is there a way to temporarily suspend a user account?     I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow.     I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesnât work.     We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesnât seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect.     Has anyone else solved this issue? Thank you! -Felipe Gasper Houston, TX -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you. [01] -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Temporarily suspend a user account?
On 6 Feb 2015 4:51 PM, David G Johnston wrote: On Fri, Feb 6, 2015 at 3:41 PM, Felipe Gasper [via PostgreSQL] [hidden email] /user/SendEmail.jtp?type=nodenode=5837007i=0wrote: So, this works when I do it manually, but not when I script it. Is it possible that this change doesn’t take effect immediately? Is there any way to tell when it does (besides just waiting until login attempts fail)? It should take effect when you commit the transaction in which you perform the update... The active sessions would remain logged in but future attempts to login would fail. Yeah now I’m wondering if DBD::Pg is doing some weird caching. Anyhow, I’ll figure it out. Thank you! :) -FG -- 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] Temporarily suspend a user account?
On 6 Feb 2015 3:15 PM, David G Johnston wrote: Felipe Gasper wrote Hello, Is there a way to temporarily suspend a user account? I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work. We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect. Has anyone else solved this issue? Thank you! Personally untested: ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in the past This doesn’t work, either, because it will clobber any custom expiration time for the role … -FG -- 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] Temporarily suspend a user account?
On Fri, Feb 6, 2015 at 2:29 PM, Felipe Gasper [via PostgreSQL] ml-node+s1045698n5836992...@n5.nabble.com wrote: On 6 Feb 2015 3:15 PM, David G Johnston wrote: Felipe Gasper wrote Hello, Is there a way to temporarily suspend a user account? I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work. We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect. Has anyone else solved this issue? Thank you! Personally untested: ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in the past This doesn’t work, either, because it will clobber any custom expiration time for the role … -FG Since everything about a role can be customized, and there is no simple enabled boolean, you need to take a known value, cache it somewhere, make your change, then restore the cached value; or just edit pg_hba.conf and add reject entries for the role in question. David J. -- View this message in context: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5836994.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Temporarily suspend a user account?
David G Johnston david.g.johns...@gmail.com writes: On Fri, Feb 6, 2015 at 3:41 PM, Felipe Gasper [via PostgreSQL] [hidden email] wrote: So, this works when I do it manually, but not when I script it. Is it possible that this change doesnât take effect immediately? Is there any way to tell when it does (besides just waiting until login attempts fail)? âIt should take effect when you commit the transaction in which you perform the update... The active sessions would remain logged in but future attempts to login would fail. Right. Nothing about disabling an account causes existing sessions to close. The OP should do... mangle password and commit; pg_terminate_backend(disabled user); WAiting a few seconds between those steps probably not a bad idea to help avoid a race if any between pw authentication and a session registering in pg_stat_activity. âDavid J. -- View this message in context: Re: Temporarily suspend a user account? Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Temporarily suspend a user account?
On Fri, Feb 6, 2015 at 2:22 PM, Michael Nolan [via PostgreSQL] ml-node+s1045698n5836989...@n5.nabble.com wrote: Might not do what you want, but I just change the password. How do you do that and re-enable using the previous password? David J. -- View this message in context: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5836990.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] Unknown error while running postgresql_installer_dc46cfee2c\getlocales.exe
Hi List, Trying to install PostgresSQL 9.4.1 on Window XP Pro Service Pack 3. Installation is aborted with the following error: Unknown error while running C:\Documents and Settings\George Weaver\Local Settings\Temp\postgresql_installer_dc46cfee2c\getlocales.exe bitrock_installer.log output below. Would this be an issue with trying to install on XP? Thanks George Log started 02/05/2015 at 11:17:31 Preferred installation mode : qt Trying to init installer in mode qt Mode qt successfully initialized Executing C:\Documents and Settings\George Weaver\Local Settings\Temp/postgresql_installer_dc46cfee2c/temp_check_comspec.bat Script exit code: 0 Script output: test ok Script stderr: Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.4 Data Directory. Setting variable iDataDirectory to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.4 Base Directory. Setting variable iBaseDirectory to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.4 Service ID. Setting variable iServiceName to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.4 Service Account. Setting variable iServiceAccount to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.4 Super User. Setting variable iSuperuser to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.4 Branding. Setting variable iBranding to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.4 Version. Setting variable brandingVer to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.4 Shortcuts. Setting variable iShortcut to empty value Could not find registry key HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.4 DisableStackBuilder. Setting variable iDisableStackBuilder to empty value [11:17:37] Existing base directory: [11:17:37] Existing data directory: [11:17:37] Using branding: PostgreSQL 9.4 [11:17:37] Using Super User: postgres and Service Account: NT AUTHORITY\NetworkService [11:17:37] Using Service Name: postgresql-9.4 Executing cscript //NoLogo C:\Documents and Settings\George Weaver\Local Settings\Temp\postgresql_installer_dc46cfee2c\prerun_checks.vbs Script exit code: 0 Script output: The scripting host appears to be functional. Script stderr: Executing C:\Documents and Settings\George Weaver\Local Settings\Temp\postgresql_installer_dc46cfee2c\vcredist_x86.exe /passive /norestart Script exit code: 0 Script output: Script stderr: Executing C:\Documents and Settings\George Weaver\Local Settings\Temp\postgresql_installer_dc46cfee2c\getlocales.exe Script exit code: Script output: Script stderr: Unknown error while running C:\Documents and Settings\George Weaver\Local Settings\Temp\postgresql_installer_dc46cfee2c\getlocales.exe
Re: [GENERAL] Temporarily suspend a user account?
On Fri, Feb 6, 2015 at 3:55 PM, Felipe Gasper [via PostgreSQL] ml-node+s1045698n5837008...@n5.nabble.com wrote: On 6 Feb 2015 4:51 PM, David G Johnston wrote: On Fri, Feb 6, 2015 at 3:41 PM, Felipe Gasper [via PostgreSQL] [hidden email] /user/SendEmail.jtp?type=nodenode=5837007i=0wrote: So, this works when I do it manually, but not when I script it. Is it possible that this change doesn’t take effect immediately? Is there any way to tell when it does (besides just waiting until login attempts fail)? It should take effect when you commit the transaction in which you perform the update... The active sessions would remain logged in but future attempts to login would fail. Yeah now I’m wondering if DBD::Pg is doing some weird caching. Anyhow, I’ll figure it out. Thank you! :) Connection pool sessions are likely remaining connected to the database longer than your application. .. Dave -- View this message in context: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5837009.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Cluster seems broken after pg_basebackup
On 02/06/2015 09:17 AM, Guillaume Drolet wrote:
Dear Adrian,
Thanks for helping me. Sorry for the lack of details, I had said to
myself I had to not forget to give these details but I hit the send
button too fast. You know how it is...
I added more info in your reply below.
First some questions:
1) What Postgres version?
9.3
Windows 7
3) Where were you backing up from and to?
Backing up from my only cluster (PGDATA) on disk E, to a backup
directory on an other disk (F:) using this command:
pg_basebackup -D F:\\db_base_backup -Fp -Xs -R -P
--label=basebackup20150205 --username=postgres
What's weird is that I did some successful tests last week on the same
system (backing up, archiving, recovering) using the same procedure.
Only difference was the cluster, which was much smaller for testing
purposes, but located at the same place (i.e. E:\data) and PostgresSQL
installed in C:\Programs\...
4) Which cluster does not start, the master or the child you created
with pg_basebackup?
The master. I haven't tried the child yet. But I saw that the message
about role 208375PT$ is in logs from before the backup too.
This is the local domain of my machine. I log onto my machine with a
local admin account and using domain name 208375PT (I didn't set this
part of my machine, the IT guys here at work did). The thing is: I don't
understand why it's there in the log file??
Not sure.
What are you using for an authentication method for database login?
And after that, I went back to the log file and there's new
information
added:
2015-02-06 07:51:05 EST LOG: processus serveur (PID 184) a été
arrêté
par l'exception 0x8004
2015-02-06 07:51:05 EST DÉTAIL: Le processus qui a échoué
exécutait :
SELECT version();
2015-02-06 07:51:05 EST ASTUCE : Voir le fichier d'en-tête C «
ntstatus.h » pour une description de la valeur
hexadécimale.
Well according to here:
https://msdn.microsoft.com/en-__us/library/cc704588.aspx
https://msdn.microsoft.com/en-us/library/cc704588.aspx
0x8004
STATUS_SINGLE_STEP
{EXCEPTION} Single Step A single step or trace operation has just
been completed.
A developer is going to have explain what that means.
My suspicion is you copied at least partly over a running server.
How would that be possible? Using the pg_basebackup command I wrote
above, it is clear that I wrote the backup on disk F and not E.
I was just speculating, I would not put too much stock in it.
While writing this post, I started my backup using:
pg_ctl start -D F:\db_basebackup
Similar stuff happened with pgAdmin and the log (message about symbolic
link is related to my post from yesterday. I don't know if this could be
involved in the current problem):
2015-02-06 12:13:58 EST LOG: le système de bases de données a été
interrompu ; dernier lancement connu à 2015-02-05 14:30:34 EST
2015-02-06 12:13:58 EST LOG: création du répertoire manquant «
pg_xlog/archive_status » pour les journaux de transactions
2015-02-06 12:13:58 EST LOG: la ré-exécution commence à 24B/2890
2015-02-06 12:13:58 EST LOG: n'a pas pu supprimer le lien symbolique «
pg_tblspc/940585 » : No such file or directory
2015-02-06 12:13:58 EST CONTEXTE : xlog redo drop tablespace: 940585
2015-02-06 12:13:58 EST LOG: état de restauration cohérent atteint à
24B/29B8
2015-02-06 12:13:58 EST LOG: ré-exécution faite à 24B/29B8
2015-02-06 12:13:58 EST LOG: la dernière transaction a eu lieu à
2015-02-05 09:06:04.892-05 (moment de la journalisation)
2015-02-06 12:13:59 EST LOG: le système de bases de données est prêt
pour accepter les connexions
2015-02-06 12:13:59 EST LOG: lancement du processus autovacuum
2015-02-06 12:14:42 EST LOG: processus serveur (PID 1784) a été arrêté
par l'exception 0x8004
2015-02-06 12:14:42 EST DÉTAIL: Le processus qui a échoué exécutait :
SELECT version();
2015-02-06 12:14:42 EST ASTUCE : Voir le fichier d'en-tête C «
ntstatus.h » pour une description de la valeur
hexadécimale.
2015-02-06 12:14:42 EST LOG: arrêt des autres processus serveur actifs
2015-02-06 12:14:42 EST ATTENTION: arrêt de la connexion à cause de
l'arrêt brutal d'un autre processus serveur
2015-02-06 12:14:42 EST DÉTAIL: Le postmaster a commandé à ce processus
serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté
anormalement
et qu'il existe probablement de la mémoire partagée corrompue.
2015-02-06 12:14:42 EST ASTUCE : Dans un moment, vous devriez être
capable de vous reconnecter à la base de
données et de relancer votre commande.
2015-02-06 12:14:42 EST LOG: tous les processus serveur se sont
arrêtés, réinitialisation
Any ideas where to go from here?
In both cases the database got to the point below, which would seem to
indicate everything was alright.
2015-02-06
Re: [GENERAL] Temporarily suspend a user account?
On 6 Feb 2015 4:31 PM, Felipe Gasper wrote: On 6 Feb 2015 4:21 PM, Jerry Sievers wrote: David G Johnston david.g.johns...@gmail.com writes: On Fri, Feb 6, 2015 at 2:29 PM, Felipe Gasper [via PostgreSQL] [hidden email] wrote: On 6 Feb 2015 3:15 PM, David G Johnston wrote: Felipe Gasper wrote Hello, Is there a way to temporarily suspend a user account? I would prefer not to revoke login privileges since that will break things that mine pg_users and pg_shadow. I also am trying to find something that is completely reversible, so something like setting connection limit to 0, which would lose a potentially customized connection limit, doesn’t work. We do this in MySQL by reversing the password hash then running FLUSH PRIVILEGES; however, that doesn’t seem to work in PostgreSQL/pg_authid as some sort of cache prevents this from taking effect. Has anyone else solved this issue? Thank you! Personally untested: ALTER ROLE role_name VALID UNTIL 'timestamp' --i.e., set that to sometime in the past This doesn’t work, either, because it will clobber any custom expiration time for the role … -FG ​Since everything about a role can be customized, and there is no simple enabled boolean, you need to take a known value, cache it somewhere, make your change, then restore the cached value; or just edit pg_hba.conf and add reject entries for the role in question. Here we go... disable: update pg_authid set rolpassword = rolpassword || '.disabled' where rolname = 'foo'; enable: update pg_authid set rolpassword = rtrim(rolpassword, 'disabled') where rolname = 'foo'; So, this works when I do it manually, but not when I script it. Is it possible that this change doesn’t take effect immediately? Is there any way to tell when it does (besides just waiting until login attempts fail)? -FG -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: [BUGS] pg_dump search path issue
I posted this to pgbugs a little while ago and I couldn't get much traction. I'm hoping that someone in the general list may be able to help me with this. Namely, this question: For contrib functions - is there even a way for embedded queries in functions to be auto-coded to the correct schema when you run CREATE EXTENSION? I know the command (CREATE EXTENSION) takes a schema name, but how does that get added to embedded queries? Is there a best practice for this? -- Forwarded message -- From: Elijah Zupancic eli...@zupancic.name Date: Wed, Feb 4, 2015 at 6:01 PM Subject: Re: [BUGS] pg_dump search path issue To: Tom Lane t...@sss.pgh.pa.us Cc: pgsql-b...@postgresql.org Hi Tom, Thanks for your reply. The functions in question are user defined inasmuch as they are from the contrib package. Here is a sample of one of the errors: psql:./prod-db-2015-02-04.sql:1688: ERROR: function cube_distance(public.earth, public.earth) does not exist LINE 1: SELECT sec_to_gc(cube_distance($1, $2)) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT sec_to_gc(cube_distance($1, $2)) CONTEXT: SQL function earth_distance during inlining SELECT 0 This happens when pg_dump tries to recreate the definition of a materialized view. When I look at the dump, I see it sets a search path like: SET search_path = aggregator, pg_catalog; Then it goes a ways and creates a bunch of tables. Then it gets to the materialized view. Upon closer inspection, the materialized view is then calling the public.earth_distance function properly. However, the earth distance function is calling an unqualified (missing the schema specifier) function like so: SELECT sec_to_gc(cube_distance($1, $2)) So, I'm with you - this is a problem with how the functions were created. They should specify the schema so that they work correctly. However, for contrib functions - is there even a way for them to be auto-coded to the correct schema when you run CREATE EXTENSION? I know the command takes a schema name, but how does that get added to embedded queries? If there, is a best practice for this, I can take a stab at patching earthdistance. Thanks, Elijah Zupancic On Wed, Feb 4, 2015 at 5:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Elijah Zupancic eli...@zupancic.name writes: In the SQL dump, you will notice that the SET search_path = xxx values will often not include the public schema which holds the functions needed to properly recreate tables that depend on extensions. All the cases I've seen of this involve user-defined functions that are broken, often dangerously so. A function should not assume that it's being called with any particular search_path; if it's intended for use in a multi-schema database, good practice is to either explicitly qualify names or use a SET clause to force the search_path to be what it expects. It seems like the code that generates the SET search_path should check to see if any of the objects it is dumping depend on functions that use the public schema. If that didn't involve solving the halting problem, we might try to do it. But for better or worse, functions in Postgres are mostly black boxes so far as callers are concerned. It's not possible for pg_dump to know that some function has an expectation of being invoked with a particular search path. regards, tom lane -- -Elijah -- -Elijah -- 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] Cluster seems broken after pg_basebackup
Le 6 févr. 2015 17:31, Adrian Klaver adrian.kla...@aklaver.com a écrit :
On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
Hi,
Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
my cluster doesn't work properly. I tried restarting the computer (or
service) a few times but I always get the same messages in my logs (it's
in French. If someone is willing to help me I can try to translate the
logs. Just ask):
Enter Google Translate:)
But first, Guillaume, do yourself and everyone else a favor: turn the dam
log into English. Set lc_messages to 'C' in postgresql.conf.
First some questions:
1) What Postgres version?
2) What OS(s)? I am assuming Windows from the log info below, but we all
know what assuming gets you.
3) Where were you backing up from and to?
4) Which cluster does not start, the master or the child you created with
pg_basebackup?
2015-02-06 07:11:38 EST LOG: le système de bases de données a été
interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
2015-02-06 07:11:38 EST LOG: le système de bases de données n'a pas été
arrêté proprement ; restauration
automatique en cours
2015-02-06 07:11:38 EST LOG: record with zero length at 24B/2C000160
2015-02-06 07:11:38 EST LOG: la ré-exécution n'est pas nécessaire
2015-02-06 07:11:38 EST LOG: le système de bases de données est prêt
pour accepter les connexions
2015-02-06 07:11:38 EST LOG: lancement du processus autovacuum
2015-02-06 07:11:38 EST FATAL: le rôle « 208375PT$ » n'existe pas
So where is role 208375PT$ supposed to come from?
Then if I start pgAdmin I get a series of pop-ups I have to click OK to
to continue:
An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: oid
An error has ocurred: Column not found in pgSet: encoding
An error has ocurred: Column not found in pgSet: Connection to database
broken
Not sure about that this, someone more versed in pgAdmin will have to
answer.
Usually you see these messages when you're using a pgadmin major release
older than a PostgreSQL make release. For a 9.3 release, that would mean a
pgadmin older than 1.18.
And after that, I went back to the log file and there's new information
added:
2015-02-06 07:51:05 EST LOG: processus serveur (PID 184) a été arrêté
par l'exception 0x8004
2015-02-06 07:51:05 EST DÉTAIL: Le processus qui a échoué exécutait :
SELECT version();
2015-02-06 07:51:05 EST ASTUCE : Voir le fichier d'en-tête C «
ntstatus.h » pour une description de la valeur
hexadécimale.
Well according to here:
https://msdn.microsoft.com/en-us/library/cc704588.aspx
0x8004
STATUS_SINGLE_STEP
{EXCEPTION} Single Step A single step or trace operation has just been
completed.
A developer is going to have explain what that means.
2015-02-06 07:51:05 EST LOG: arrêt des autres processus serveur actifs
2015-02-06 07:51:05 EST ATTENTION: arrêt de la connexion à cause de
l'arrêt brutal d'un autre processus serveur
2015-02-06 07:51:05 EST DÉTAIL: Le postmaster a commandé à ce processus
serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté
anormalement
et qu'il existe probablement de la mémoire partagée corrompue.
2015-02-06 07:51:05 EST ASTUCE : Dans un moment, vous devriez être
capable de vous reconnecter à la base de
données et de relancer votre commande.
2015-02-06 07:51:05 EST LOG: processus d'archivage (PID 692) quitte
avec le code de sortie 1
2015-02-06 07:51:05 EST LOG: tous les processus serveur se sont
arrêtés, réinitialisation
2015-02-06 07:51:15 EST FATAL: le bloc de mémoire partagé pré-existant
est toujours en cours d'utilisation
2015-02-06 07:51:15 EST ASTUCE : Vérifier s'il n'y a pas de vieux
processus serveur en cours d'exécution. Si c'est le
cas, fermez-les.
I was about to try restarting postgresql using the base backup I made
yesterday but since this means I'll have to copy my database again (700
GB takes a while...) I am looking for a better solution from more
experienced people.
My suspicion is you copied at least partly over a running server.
[GENERAL] PgConf Russia event
Hi, community members, on behalf of PgConf Russia local commitee, program commitee and russian postgresql group I apologize about the issue with including inappropriate dancers into conference social event. The issue was caused by the lack of control on the company which was responsible for the organizing the entertainment. We regret about this untolerable incindent and we would like to assure everyone that in the future such things will never happen again. -- Best regards, Alexey Slynko, LOC PgConf 2015 -- 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] Temporarily suspend a user account?
On 6 Feb 2015 4:04 PM, Michael Nolan wrote: Encrypted passwords are kept in the pg_shadow file and should start with 'md5'. Just save a copy of the encrypted password for that user and when you want to re-enable that user do: alter user xxx encrypted password 'md5'; I have tested this on 9.3.5. That’s basically what I tried before, though I just string-reversed the MD5 hash so that I could un-reverse it. I also prefixed “md5” with '-' so I could tell which passwords were scrambled. What I found was that a “suspended” user could still log in, though. I looked for some control to “reload” the passwords from that datastore but couldn’t find any. I also did this on pg_authid; would that have made a difference? -FG -- 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] Temporarily suspend a user account?
On Fri, Feb 6, 2015 at 3:41 PM, Felipe Gasper [via PostgreSQL] ml-node+s1045698n5837006...@n5.nabble.com wrote: So, this works when I do it manually, but not when I script it. Is it possible that this change doesn’t take effect immediately? Is there any way to tell when it does (besides just waiting until login attempts fail)? It should take effect when you commit the transaction in which you perform the update... The active sessions would remain logged in but future attempts to login would fail. David J. -- View this message in context: http://postgresql.nabble.com/Temporarily-suspend-a-user-account-tp5836978p5837007.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On Friday, February 6, 2015, Adrian Klaver adrian.kla...@aklaver.com
wrote:
On 02/06/2015 10:26 AM, Tim Smith wrote:
Re:So, you have an input parameter named session_id and a query with
a column named session_id - this is the problem.
Well, I'll re-try with a revised function, but surely the database
could have come up with a more meaningful and insightful message than
the coded incomprehensible error message it did ?I would say its
not only user error, its developer error too for creating such
confusing error messages !
Well actually you did yourself a disservice by including the EXCEPT code.
That changed the error message. Taking that code out and running the
failing function you get:
test- validateSession('441122','10.11.12.13','abc',3600,3600);
ERROR: column reference session_id is ambiguous
LINE 2: session_id=session_id and session_ip=client_ip
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: select * from app_val_session_vw where
session_id=session_id and session_ip=client_ip
CONTEXT: PL/pgsql function
validatesession(character,inet,character,bigint,bigint)
line 7 at SQL statement
I would say that is fairly specific:)
The exception block is ok, you want to report the session-id passed (via
raise notice or similar), but you want to use the RAISE; form (i.e., no
args) to re-raise the original error.
http://www.postgresql.org/docs/9.4/interactive/plpgsql-errors-and-messages.html
David J.
Re: [GENERAL] Partioning with overlapping and non overlapping constraints
Perhaps, I do not fully understand completely, but would it not be simpler to just rearrange the key (and partition) by date location? EG: 2015_01_01_metrics_location_X In that way, you would only have 365 partitions per year at most. But you also have the option to break it down by week or month, or year. EG: EXTRACT(YEAR FROM utc_time) = 2015 AND EXTRACT(WEEK FROM utc_time) = 1 or EXTRACT(YEAR FROM utc_time) = 2015 AND EXTRACT(MONTH FROM utc_time) = 1 or just EXTRACT(YEAR FROM utc_time) = 2015 On Thu, Feb 5, 2015 at 10:12 PM, David G Johnston david.g.johns...@gmail.com wrote: Tim Uckun wrote 1. Should I be worried about having possibly hundreds of thousands of shards. IIRC, yes. 2. Is PG smart enough to handle overlapping constraints on table and limit it's querying to only those tables that have the correct time constraint. Probably yes, but seems easy enough to verify. All constraints are checked for each partiton and if any return false the entire partiton will be excluded; which means multiple partitions can be included. Note, this is large reason why #1 poses a problem. David J. -- View this message in context: http://postgresql.nabble.com/Partioning-with-overlapping-and-non-overlapping-constraints-tp5836869p5836871.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 -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
And if you want my exact version of Postgres its PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit (taken from the Postgres APT repository) -- 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] Using row_to_json with %ROWTYPE ?
On 02/06/2015 04:19 AM, Tim Smith wrote:
Alright then, here you go ... Postgres 9.4
We start with a clean database :
json_return_debugdb= \dn
List of schemas
Name | Owner
+--
public | postgres
(1 row)
json_return_debugdb= \dt
No relations found.
json_return_debugdb= \dv
No relations found.
We replicate a basic version of app_val_session_vw:
create table app_sessions (session_id bigint primary key,user_id
bigint unique not null, session_ip inet);
create table app_users (user_id bigint primary key,user_name text,
user_active boolean not null);
create view app_users_vw as select * from app_users where user_active=true;
create view app_val_session_vw as select
a.session_id,a.session_ip,b.user_name,b.user_id from app_sessions a,
app_users b where a.user_id=b.user_id;
We insert data :
insert into app_users values(1,’Foobar',true);
insert into app_sessions(441122,1,’10.11.12.13’,);
json_return_debugdb= select
validateSession('441122','10.11.12.13','abc',3600,3600);
ERROR: Failed to validate session for session 441122 (SQLSTATE: 42702
- SQLERRM: column reference session_id is ambiguous)
HINT: Database error occured (sval fail)
Unfortunately the function definition is not given and that is where you
are seeing the error. To figure this out we will need to see the function.
--
Adrian Klaver
adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
Alright then, here you go ... Postgres 9.4
We start with a clean database :
json_return_debugdb= \dn
List of schemas
Name | Owner
+--
public | postgres
(1 row)
json_return_debugdb= \dt
No relations found.
json_return_debugdb= \dv
No relations found.
We replicate a basic version of app_val_session_vw:
create table app_sessions (session_id bigint primary key,user_id
bigint unique not null, session_ip inet);
create table app_users (user_id bigint primary key,user_name text,
user_active boolean not null);
create view app_users_vw as select * from app_users where user_active=true;
create view app_val_session_vw as select
a.session_id,a.session_ip,b.user_name,b.user_id from app_sessions a,
app_users b where a.user_id=b.user_id;
We insert data :
insert into app_users values(1,’Foobar',true);
insert into app_sessions(441122,1,’10.11.12.13’,);
json_return_debugdb= select
validateSession('441122','10.11.12.13','abc',3600,3600);
ERROR: Failed to validate session for session 441122 (SQLSTATE: 42702
- SQLERRM: column reference session_id is ambiguous)
HINT: Database error occured (sval fail)
On 5 February 2015 at 23:58, Adrian Klaver adrian.kla...@aklaver.com wrote:
On 02/05/2015 03:25 PM, Tim Smith wrote:
PostgreSQL doesn't lie
Well if its not lying its one big stinking bug !
In my experience Postgres does not randomly make up error messages.
Somewhere it is seeing a duplicate column.
How about you tell me where you see these duplicate columns in my view
that PostgreSQL is apparently not lying to me about
So then this is not the problem, which moves the troubleshooting to the
function.
Have you tried the previous suggestions on modifying the function?
--
Adrian Klaver
adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
Unfortunately the function definition is not given and that is where you are seeing the error. To figure this out we will need to see the function. Geez, there's just no satisfying some people ! ;-) I did actually show you my function in an earlier mail but my current bodged minimised version looks like this : CREATE FUNCTION validateSession(session_id char(64),client_ip inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint) RETURNS json AS $$ DECLARE v_now bigint; v_row app_val_session_vw%ROWTYPE; BEGIN v_now := extract(epoch FROM now())::bigint; select * into strict v_row from app_val_session_vw where session_id=session_id and session_ip=client_ip; RETURN row_to_json(v_row); EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: % - SQLERRM: %)', session_id,SQLSTATE,SQLERRM USING HINT = 'Database error occured (sval fail)'; END; $$ LANGUAGE plpgsql; Note that I have tried a million and one different versions of the line RETURN row_to_json(v_row); including declaring a JSON type var and putting hte result into that before returning. But nothing works, it always comes back with the same session_id nonsense. -- 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] Cluster seems broken after pg_basebackup
Dear Adrian,
Thanks for helping me. Sorry for the lack of details, I had said to myself
I had to not forget to give these details but I hit the send button too
fast. You know how it is...
I added more info in your reply below.
2015-02-06 11:28 GMT-05:00 Adrian Klaver adrian.kla...@aklaver.com:
On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
Hi,
Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
my cluster doesn't work properly. I tried restarting the computer (or
service) a few times but I always get the same messages in my logs (it's
in French. If someone is willing to help me I can try to translate the
logs. Just ask):
Enter Google Translate:)
Not a big fan. I've seen bad misunderstandings happen there!
First some questions:
1) What Postgres version?
9.3
2) What OS(s)? I am assuming Windows from the log info below, but we all
know what assuming gets you.
Windows 7
3) Where were you backing up from and to?
Backing up from my only cluster (PGDATA) on disk E, to a backup directory
on an other disk (F:) using this command:
pg_basebackup -D F:\\db_base_backup -Fp -Xs -R -P
--label=basebackup20150205 --username=postgres
What's weird is that I did some successful tests last week on the same
system (backing up, archiving, recovering) using the same procedure. Only
difference was the cluster, which was much smaller for testing purposes,
but located at the same place (i.e. E:\data) and PostgresSQL installed in
C:\Programs\...
4) Which cluster does not start, the master or the child you created with
pg_basebackup?
The master. I haven't tried the child yet. But I saw that the message about
role 208375PT$ is in logs from before the backup too.
2015-02-06 07:11:38 EST LOG: le système de bases de données a été
interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
2015-02-06 07:11:38 EST LOG: le système de bases de données n'a pas été
arrêté proprement ; restauration
automatique en cours
2015-02-06 07:11:38 EST LOG: record with zero length at 24B/2C000160
2015-02-06 07:11:38 EST LOG: la ré-exécution n'est pas nécessaire
2015-02-06 07:11:38 EST LOG: le système de bases de données est prêt
pour accepter les connexions
2015-02-06 07:11:38 EST LOG: lancement du processus autovacuum
2015-02-06 07:11:38 EST FATAL: le rôle « 208375PT$ » n'existe pas
So where is role 208375PT$ supposed to come from?
This is the local domain of my machine. I log onto my machine with a local
admin account and using domain name 208375PT (I didn't set this part of my
machine, the IT guys here at work did). The thing is: I don't understand
why it's there in the log file??
Then if I start pgAdmin I get a series of pop-ups I have to click OK to
to continue:
An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: oid
An error has ocurred: Column not found in pgSet: encoding
An error has ocurred: Column not found in pgSet: Connection to database
broken
Not sure about that this, someone more versed in pgAdmin will have to
answer.
And after that, I went back to the log file and there's new information
added:
2015-02-06 07:51:05 EST LOG: processus serveur (PID 184) a été arrêté
par l'exception 0x8004
2015-02-06 07:51:05 EST DÉTAIL: Le processus qui a échoué exécutait :
SELECT version();
2015-02-06 07:51:05 EST ASTUCE : Voir le fichier d'en-tête C «
ntstatus.h » pour une description de la valeur
hexadécimale.
Well according to here:
https://msdn.microsoft.com/en-us/library/cc704588.aspx
0x8004
STATUS_SINGLE_STEP
{EXCEPTION} Single Step A single step or trace operation has just been
completed.
A developer is going to have explain what that means.
2015-02-06 07:51:05 EST LOG: arrêt des autres processus serveur actifs
2015-02-06 07:51:05 EST ATTENTION: arrêt de la connexion à cause de
l'arrêt brutal d'un autre processus serveur
2015-02-06 07:51:05 EST DÉTAIL: Le postmaster a commandé à ce processus
serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté
anormalement
et qu'il existe probablement de la mémoire partagée corrompue.
2015-02-06 07:51:05 EST ASTUCE : Dans un moment, vous devriez être
capable de vous reconnecter à la base de
données et de relancer votre commande.
2015-02-06 07:51:05 EST LOG: processus d'archivage (PID 692) quitte
avec le code de sortie 1
2015-02-06 07:51:05 EST LOG: tous les processus serveur se sont
arrêtés, réinitialisation
2015-02-06 07:51:15 EST FATAL: le bloc de mémoire partagé pré-existant
est toujours en cours d'utilisation
2015-02-06 07:51:15 EST ASTUCE : Vérifier s'il n'y a pas de vieux
processus serveur en cours d'exécution. Si c'est le
cas, fermez-les.
I was about to try restarting postgresql using the base backup I made
yesterday but since this
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On Fri, Feb 6, 2015 at 9:55 AM, Tim Smith randomdev4+postg...@gmail.com wrote: Unfortunately the function definition is not given and that is where you are seeing the error. To figure this out we will need to see the function. Geez, there's just no satisfying some people ! ;-) I did actually show you my function in an earlier mail but my current bodged minimised version looks like this : CREATE FUNCTION validateSession(session_id char(64),client_ip inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint) RETURNS json AS $$ DECLARE v_now bigint; v_row app_val_session_vw%ROWTYPE; BEGIN v_now := extract(epoch FROM now())::bigint; select * into strict v_row from app_val_session_vw where session_id=session_id and session_ip=client_ip; RETURN row_to_json(v_row); EXCEPTION WHEN OTHERS THEN RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: % - SQLERRM: %)', session_id,SQLSTATE,SQLERRM USING HINT = 'Database error occured (sval fail)'; END; $$ LANGUAGE plpgsql; Note that I have tried a million and one different versions of the line RETURN row_to_json(v_row); including declaring a JSON type var and putting hte result into that before returning. But nothing works, it always comes back with the same session_id nonsense. So, you have an input parameter named session_id and a query with a column named session_id - this is the problem. The function never even gets to execute the RETURN statement - the exception occurred first - so whatever you were doing there was pointless. On a side note It seems you missed the memo about the char type being largely deprecated...and furthermore if I rename the function signature session_id to i_session_id and replace the corresponding value in the SELECT statement I now get operator does not exist: bigint = character. So you've setup an input type that differs from your column type. So, yes, it is user error and while it was not due to the view that was all the information you provided at the time. I'm not in the mood to fix these two items (name and type) and find the next oversight. I do suggest that, especially if you do not use IN/OUT arguments, you prefix your function argument names with something so that you eliminate the chance that a function variable and a query variable name collide. The main give-away here was the where clause expression WHERE session_id = session_id - how would you expect PostgreSQL to know which one is from the table and which one is from the function? The only other option is to pick one of them but in that case you'd simply get a constant TRUE and every row would be returned. David J.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On 02/06/2015 08:55 AM, Tim Smith wrote:
Unfortunately the function definition is not given and that is where you are
seeing the error.
To figure this out we will need to see the function.
Geez, there's just no satisfying some people ! ;-)
I did actually show you my function in an earlier mail but my
current bodged minimised version looks like this :
CREATE FUNCTION validateSession(session_id char(64),client_ip
inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint)
RETURNS json AS $$
DECLARE
v_now bigint;
v_row app_val_session_vw%ROWTYPE;
BEGIN
v_now := extract(epoch FROM now())::bigint;
select * into strict v_row from app_val_session_vw where
session_id=session_id and session_ip=client_ip;
RETURN row_to_json(v_row);
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
- SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
END;
$$ LANGUAGE plpgsql;
Note that I have tried a million and one different versions of the
line RETURN row_to_json(v_row); including declaring a JSON type
var and putting hte result into that before returning. But nothing
works, it always comes back with the same session_id nonsense.
Changed to work:
CREATE OR REPLACE FUNCTION public.validatesession(s_id character,
client_ip inet, user_agent character, forcedtimeout bigint,
sessiontimeout bigint)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
v_now bigint;
v_row app_val_session_vw %ROWTYPE;
BEGIN
v_now := extract(epoch FROM now())::bigint;
select * into strict v_row from app_val_session_vw AS vw where
vw.session_id=s_id::int and session_ip=client_ip;
RETURN row_to_json(v_row);
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to validate session for session %
(SQLSTATE: %
- SQLERRM: %)', v_row.session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
END;
$function$
test=# select
validateSession('441122','10.11.12.13','abc',3600,3600);
validatesession
---
{session_id:441122,session_ip:10.11.12.13,user_name:Foobar,user_id:1}
(1 row)
The problem was a conflict between the session_id argument/variable
passed in and the session_id field in app_val_session_vw.
--
Adrian Klaver
adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
Von: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] im Auftrag von David Johnston [david.g.johns...@gmail.com] Gesendet: Freitag, 6. Februar 2015 00:38 An: Tim Smith Cc: Adrian Klaver; pgsql-general Betreff: Re: [GENERAL] Using row_to_json with %ROWTYPE ? On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith randomdev4+postg...@gmail.com wrote: You're most welcome to look at my view definition view if you don't believe me View definition: SELECT a.session_id, a.session_ip, a.session_user_agent, a.session_start, a.session_lastactive, b.user_id, b.tenant_id, b.reseller_id, b.tenant_name, b.user_fname, b.user_lname, b.user_email, b.user_phone, b.user_seed, b.user_passwd, b.user_lastupdate, b.tenant_lastupdate FROM app_sessions a, app_users_vw b WHERE a.user_id = b.user_id; ?So that view and definition are correct. So either PostgreSQL is seeing a different view (in a different schema) or the function is confused in ways difficult to predict. I guess it is possible that: (SELECT v_?row FROM v_row) would give that message but I get a relation v_row does not exist error when trying to replicate the scenario. ?It may even be a bug but since you have not provided a self-contained test case, nor the version of PostgreSQL, the assumption is user error.? David J. Hello, I don't know if there is some internal confusion when using the ROWTYPE (bug?) but if this helps, following function is equivalent and does the job: create or replace function doStuff() returns json as $$ select row_to_json(app_val_session_vw) from app_val_session_vw WHERE ...; $$ LANGUAGE sql;
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
Nice work-around Marc. Thank you ! On 6 February 2015 at 13:01, Marc Mamin m.ma...@intershop.de wrote: Von: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] im Auftrag von David Johnston [david.g.johns...@gmail.com] Gesendet: Freitag, 6. Februar 2015 00:38 An: Tim Smith Cc: Adrian Klaver; pgsql-general Betreff: Re: [GENERAL] Using row_to_json with %ROWTYPE ? On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith randomdev4+postg...@gmail.com wrote: You're most welcome to look at my view definition view if you don't believe me View definition: SELECT a.session_id, a.session_ip, a.session_user_agent, a.session_start, a.session_lastactive, b.user_id, b.tenant_id, b.reseller_id, b.tenant_name, b.user_fname, b.user_lname, b.user_email, b.user_phone, b.user_seed, b.user_passwd, b.user_lastupdate, b.tenant_lastupdate FROM app_sessions a, app_users_vw b WHERE a.user_id = b.user_id; ?So that view and definition are correct. So either PostgreSQL is seeing a different view (in a different schema) or the function is confused in ways difficult to predict. I guess it is possible that: (SELECT v_?row FROM v_row) would give that message but I get a relation v_row does not exist error when trying to replicate the scenario. ?It may even be a bug but since you have not provided a self-contained test case, nor the version of PostgreSQL, the assumption is user error.? David J. Hello, I don't know if there is some internal confusion when using the ROWTYPE (bug?) but if this helps, following function is equivalent and does the job: create or replace function doStuff() returns json as $$ select row_to_json(app_val_session_vw) from app_val_session_vw WHERE ...; $$ LANGUAGE sql; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cluster seems broken after pg_basebackup
Hi, Yesterday I ran a pg_basebackup of my cluster. Since it has completed, my cluster doesn't work properly. I tried restarting the computer (or service) a few times but I always get the same messages in my logs (it's in French. If someone is willing to help me I can try to translate the logs. Just ask): 2015-02-06 07:11:38 EST LOG: le système de bases de données a été interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST 2015-02-06 07:11:38 EST LOG: le système de bases de données n'a pas été arrêté proprement ; restauration automatique en cours 2015-02-06 07:11:38 EST LOG: record with zero length at 24B/2C000160 2015-02-06 07:11:38 EST LOG: la ré-exécution n'est pas nécessaire 2015-02-06 07:11:38 EST LOG: le système de bases de données est prêt pour accepter les connexions 2015-02-06 07:11:38 EST LOG: lancement du processus autovacuum 2015-02-06 07:11:38 EST FATAL: le rôle « 208375PT$ » n'existe pas Then if I start pgAdmin I get a series of pop-ups I have to click OK to to continue: An error has ocurred: Column not found in pgSet: datlastsysoid An error has ocurred: Column not found in pgSet: datlastsysoid An error has ocurred: Column not found in pgSet: oid An error has ocurred: Column not found in pgSet: encoding An error has ocurred: Column not found in pgSet: Connection to database broken And after that, I went back to the log file and there's new information added: 2015-02-06 07:51:05 EST LOG: processus serveur (PID 184) a été arrêté par l'exception 0x8004 2015-02-06 07:51:05 EST DÉTAIL: Le processus qui a échoué exécutait : SELECT version(); 2015-02-06 07:51:05 EST ASTUCE : Voir le fichier d'en-tête C « ntstatus.h » pour une description de la valeur hexadécimale. 2015-02-06 07:51:05 EST LOG: arrêt des autres processus serveur actifs 2015-02-06 07:51:05 EST ATTENTION: arrêt de la connexion à cause de l'arrêt brutal d'un autre processus serveur 2015-02-06 07:51:05 EST DÉTAIL: Le postmaster a commandé à ce processus serveur d'annuler la transaction courante et de quitter car un autre processus serveur a quitté anormalement et qu'il existe probablement de la mémoire partagée corrompue. 2015-02-06 07:51:05 EST ASTUCE : Dans un moment, vous devriez être capable de vous reconnecter à la base de données et de relancer votre commande. 2015-02-06 07:51:05 EST LOG: processus d'archivage (PID 692) quitte avec le code de sortie 1 2015-02-06 07:51:05 EST LOG: tous les processus serveur se sont arrêtés, réinitialisation 2015-02-06 07:51:15 EST FATAL: le bloc de mémoire partagé pré-existant est toujours en cours d'utilisation 2015-02-06 07:51:15 EST ASTUCE : Vérifier s'il n'y a pas de vieux processus serveur en cours d'exécution. Si c'est le cas, fermez-les. I was about to try restarting postgresql using the base backup I made yesterday but since this means I'll have to copy my database again (700 GB takes a while...) I am looking for a better solution from more experienced people. Thanks a lot for helping! Guillaume
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On Fri, Feb 6, 2015 at 10:23 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: CREATE FUNCTION validateSession(session_id char(64),client_ip inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint) RETURNS json AS $$ CREATE OR REPLACE FUNCTION public.validatesession(s_id character, client_ip inet, user_agent character, forcedtimeout bigint, sessiontimeout bigint) RETURNS json As an aside, and going from memory, you will note that Adrian kept the character type in the function signature but removed the length specifier. PostgreSQL does not store that information and so will not prevent a call from passing in a string longer than 64 characters into the function. This applies to any specification in () following a type declaration (say for numeric or timestamptz) David J.
Re: [GENERAL] Cluster seems broken after pg_basebackup
On 02/06/2015 05:03 AM, Guillaume Drolet wrote:
Hi,
Yesterday I ran a pg_basebackup of my cluster. Since it has completed,
my cluster doesn't work properly. I tried restarting the computer (or
service) a few times but I always get the same messages in my logs (it's
in French. If someone is willing to help me I can try to translate the
logs. Just ask):
Enter Google Translate:)
First some questions:
1) What Postgres version?
2) What OS(s)? I am assuming Windows from the log info below, but we all
know what assuming gets you.
3) Where were you backing up from and to?
4) Which cluster does not start, the master or the child you created
with pg_basebackup?
2015-02-06 07:11:38 EST LOG: le système de bases de données a été
interrompu ; dernier lancement connu à 2015-02-06 07:05:05 EST
2015-02-06 07:11:38 EST LOG: le système de bases de données n'a pas été
arrêté proprement ; restauration
automatique en cours
2015-02-06 07:11:38 EST LOG: record with zero length at 24B/2C000160
2015-02-06 07:11:38 EST LOG: la ré-exécution n'est pas nécessaire
2015-02-06 07:11:38 EST LOG: le système de bases de données est prêt
pour accepter les connexions
2015-02-06 07:11:38 EST LOG: lancement du processus autovacuum
2015-02-06 07:11:38 EST FATAL: le rôle « 208375PT$ » n'existe pas
So where is role 208375PT$ supposed to come from?
Then if I start pgAdmin I get a series of pop-ups I have to click OK to
to continue:
An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: datlastsysoid
An error has ocurred: Column not found in pgSet: oid
An error has ocurred: Column not found in pgSet: encoding
An error has ocurred: Column not found in pgSet: Connection to database
broken
Not sure about that this, someone more versed in pgAdmin will have to
answer.
And after that, I went back to the log file and there's new information
added:
2015-02-06 07:51:05 EST LOG: processus serveur (PID 184) a été arrêté
par l'exception 0x8004
2015-02-06 07:51:05 EST DÉTAIL: Le processus qui a échoué exécutait :
SELECT version();
2015-02-06 07:51:05 EST ASTUCE : Voir le fichier d'en-tête C «
ntstatus.h » pour une description de la valeur
hexadécimale.
Well according to here:
https://msdn.microsoft.com/en-us/library/cc704588.aspx
0x8004
STATUS_SINGLE_STEP
{EXCEPTION} Single Step A single step or trace operation has just been
completed.
A developer is going to have explain what that means.
2015-02-06 07:51:05 EST LOG: arrêt des autres processus serveur actifs
2015-02-06 07:51:05 EST ATTENTION: arrêt de la connexion à cause de
l'arrêt brutal d'un autre processus serveur
2015-02-06 07:51:05 EST DÉTAIL: Le postmaster a commandé à ce processus
serveur d'annuler la transaction
courante et de quitter car un autre processus serveur a quitté
anormalement
et qu'il existe probablement de la mémoire partagée corrompue.
2015-02-06 07:51:05 EST ASTUCE : Dans un moment, vous devriez être
capable de vous reconnecter à la base de
données et de relancer votre commande.
2015-02-06 07:51:05 EST LOG: processus d'archivage (PID 692) quitte
avec le code de sortie 1
2015-02-06 07:51:05 EST LOG: tous les processus serveur se sont
arrêtés, réinitialisation
2015-02-06 07:51:15 EST FATAL: le bloc de mémoire partagé pré-existant
est toujours en cours d'utilisation
2015-02-06 07:51:15 EST ASTUCE : Vérifier s'il n'y a pas de vieux
processus serveur en cours d'exécution. Si c'est le
cas, fermez-les.
I was about to try restarting postgresql using the base backup I made
yesterday but since this means I'll have to copy my database again (700
GB takes a while...) I am looking for a better solution from more
experienced people.
My suspicion is you copied at least partly over a running server.
Thanks a lot for helping! Guillaume
--
Adrian Klaver
adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
On 02/06/2015 05:33 AM, Tim Smith wrote:
Nice work-around Marc. Thank you !
Nice that it works, but in the end it proves that the issue is not with
row_to_json and a row type, but with how %ROW_TYPE is being used in a
specific function. To prove it, using your earlier function modified for
your latest test case:
CREATE OR REPLACE FUNCTION public.dostuff()
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
v_row app_val_session_vw %ROWTYPE;
j_return json;
BEGIN
select * into strict v_row from app_val_session_vw where user_id=1;
select into j_return row_to_json(v_row);
RETURN j_return;
END;
$function$
test=# select dostuff();
dostuff
---
{session_id:441122,session_ip:10.11.12.13,user_name:Foobar,user_id:1}
(1 row)
--
Adrian Klaver
adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
Re:So, you have an input parameter named session_id and a query with a column named session_id - this is the problem. Well, I'll re-try with a revised function, but surely the database could have come up with a more meaningful and insightful message than the coded incomprehensible error message it did ?I would say its not only user error, its developer error too for creating such confusing error messages ! -- 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] Using row_to_json with %ROWTYPE ?
Thank you Adrian. Will give this a go over the weekend.
On 6 February 2015 at 17:23, Adrian Klaver adrian.kla...@aklaver.com wrote:
On 02/06/2015 08:55 AM, Tim Smith wrote:
Unfortunately the function definition is not given and that is where you
are seeing the error.
To figure this out we will need to see the function.
Geez, there's just no satisfying some people ! ;-)
I did actually show you my function in an earlier mail but my
current bodged minimised version looks like this :
CREATE FUNCTION validateSession(session_id char(64),client_ip
inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint)
RETURNS json AS $$
DECLARE
v_now bigint;
v_row app_val_session_vw%ROWTYPE;
BEGIN
v_now := extract(epoch FROM now())::bigint;
select * into strict v_row from app_val_session_vw where
session_id=session_id and session_ip=client_ip;
RETURN row_to_json(v_row);
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
- SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
END;
$$ LANGUAGE plpgsql;
Note that I have tried a million and one different versions of the
line RETURN row_to_json(v_row); including declaring a JSON type
var and putting hte result into that before returning. But nothing
works, it always comes back with the same session_id nonsense.
Changed to work:
CREATE OR REPLACE FUNCTION public.validatesession(s_id character, client_ip
inet, user_agent character, forcedtimeout bigint, sessiontimeout bigint)
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
v_now bigint;
v_row app_val_session_vw %ROWTYPE;
BEGIN
v_now := extract(epoch FROM now())::bigint;
select * into strict v_row from app_val_session_vw AS vw where
vw.session_id=s_id::int and session_ip=client_ip;
RETURN row_to_json(v_row);
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE:
%
- SQLERRM: %)', v_row.session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
END;
$function$
test=# select
validateSession('441122','10.11.12.13','abc',3600,3600);
validatesession
---
{session_id:441122,session_ip:10.11.12.13,user_name:Foobar,user_id:1}
(1 row)
The problem was a conflict between the session_id argument/variable passed
in and the session_id field in app_val_session_vw.
--
Adrian Klaver
adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
On 02/06/2015 10:26 AM, Tim Smith wrote:
Re:So, you have an input parameter named session_id and a query with
a column named session_id - this is the problem.
Well, I'll re-try with a revised function, but surely the database
could have come up with a more meaningful and insightful message than
the coded incomprehensible error message it did ?I would say its
not only user error, its developer error too for creating such
confusing error messages !
Well actually you did yourself a disservice by including the EXCEPT
code. That changed the error message. Taking that code out and running
the failing function you get:
test- validateSession('441122','10.11.12.13','abc',3600,3600);
ERROR: column reference session_id is ambiguous
LINE 2: session_id=session_id and session_ip=client_ip
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: select * from app_val_session_vw where
session_id=session_id and session_ip=client_ip
CONTEXT: PL/pgSQL function
validatesession(character,inet,character,bigint,bigint) line 7 at SQL
statement
I would say that is fairly specific:)
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
