Re: [GENERAL] Postgres csv logging
Hi David, thank you for your reply.
David G Johnston wrote
> As the comment there says your config and your output seem at odds.
What I showed was the output present in the csv log, the output in the text
file log has the prefix that I indicated in the configration file.
Either way, I managed to "solve" my problem, I started playing with the
logging configurations and managed to get it working. I still have no clue
why a thing so basic isn't correctly implemented.
In a simple way, what I wanted to do was to configure postgres so it would
log all the queries and respective types in a file, like this
"SELECT * FROM test","SELECT"
"INSERT INTO test (id, time) VALUES ('123','1-1-2010')","INSERT"
...
Now, when checking the official documentation regarding postgres logging, I
noticed that the csv format had all the information I needed
(https://www.postgresql.org/docs/9.4/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG)
In theory, logging to a csv file would give a lot of information that would
be usefull to me, return code, query, command tag, etc... In practice, most
of the columns are missing... Not very usefull at all. I started playing
with the logging configuration and managed to have an output that I can use.
By setting the next variables:
log_min_duration_statement = 0
log_statement = 'none'
I managed to create an output with the actual command tag of the query
instead of the word "idle". Funny part, if I change the setting
log_statement to anything else than none, all the command tags are set to
"idle"... As I already said, fields are missing, for instance, I have no
query... I had to use some regex to get it from the message field, but it
should be present in the respective column, but it isn't.
Again, this is something that seems very simple to do... and I thinks its
also very usefull. Worst part is that it is documented, so why is not
possible to do such a trivial task??
--
View this message in context:
http://www.postgresql-archive.org/Postgres-csv-logging-tp5972017p5972482.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] Backward compatibility
On Fri, Jul 21, 2017 at 10:21 PM, Igor Korot wrote: > Hi, guys, > Below query does not even run: > > SELECT version(), substring( version() from position( '\s' in version() ) > ); > > Could you spot the error? > > works for me. psql psql (9.5.7) Type "help" for help. joarmc=# SELECT version(), substring( version() from position( '\s' in version() ) ); version | substring -+ - PostgreSQL 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit | PostgreSQL 9.5.7 on x86_64-redhat-linux-gnu, co mpiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit (1 row) -- Veni, Vidi, VISA: I came, I saw, I did a little shopping. Maranatha! <>< John McKown
Re: [GENERAL] Backward compatibility
Hi, John, On Sat, Jul 22, 2017 at 8:44 AM, John McKown wrote: > On Fri, Jul 21, 2017 at 10:21 PM, Igor Korot wrote: >> >> Hi, guys, >> Below query does not even run: >> >> SELECT version(), substring( version() from position( '\s' in version() ) >> ); >> >> Could you spot the error? >> > > works for me. > > psql > psql (9.5.7) > Type "help" for help. > > joarmc=# SELECT version(), substring( version() from position( '\s' in > version() ) ); > version > | > substring > -+ > - > PostgreSQL 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 > 20161221 (Red Hat 6.3.1-1), 64-bit | PostgreSQL 9.5.7 on > x86_64-redhat-linux-gnu, co > mpiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit > (1 row) Weird. I started a new session of psql and it now works. But it works incorrectly - it should return: 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit i.e. without the word "PosgreSQL", since '\s' should match the (first) space in the version(). Thank you. > > > > -- > Veni, Vidi, VISA: I came, I saw, I did a little shopping. > > Maranatha! <>< > John McKown -- 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] Backward compatibility
Igor Korot writes: > But it works incorrectly - it should return: > 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221 > (Red Hat 6.3.1-1), 64-bit > i.e. without the word "PosgreSQL", since '\s' should match the (first) > space in the version(). position() is not a regex operation, it's just a plain substring match. regression=# SELECT position( '\s' in version() ) ; position -- 0 (1 row) You hardly need any flexibility for this anyway, so I'd just do regression=# SELECT position( ' ' in version() ) ; position -- 11 (1 row) Although possibly what you really want is split_part(). regression=# select split_part(version(), ' ', 2); split_part 9.5.7 (1 row) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
On Sat, 2017-07-22 at 10:49 -0400, Tom Lane wrote: > Igor Korot writes: > > But it works incorrectly - it should return: > > 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 > > 20161221 > > (Red Hat 6.3.1-1), 64-bit > > i.e. without the word "PosgreSQL", since '\s' should match the > > (first) > > space in the version(). > > position() is not a regex operation, it's just a plain substring > match. > > regression=# SELECT position( '\s' in version() ) ; > position > -- > 0 > (1 row) > > You hardly need any flexibility for this anyway, so I'd just do > > regression=# SELECT position( ' ' in version() ) ; > position > -- > 11 > (1 row) > > Although possibly what you really want is split_part(). > > regression=# select split_part(version(), ' ', 2); > split_part > > 9.5.7 > (1 row) > > regards, tom lane > > An alternative select:- SELECT version(), (regexp_split_to_array( version(), E'\\s+'))[2] Cheers, Rob -- 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] Backward compatibility
Thx. The split_part() works perfectly. On Sat, Jul 22, 2017 at 10:49 AM, Tom Lane wrote: > Igor Korot writes: >> But it works incorrectly - it should return: >> 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221 >> (Red Hat 6.3.1-1), 64-bit >> i.e. without the word "PosgreSQL", since '\s' should match the (first) >> space in the version(). > > position() is not a regex operation, it's just a plain substring match. > > regression=# SELECT position( '\s' in version() ) ; > position > -- > 0 > (1 row) > > You hardly need any flexibility for this anyway, so I'd just do > > regression=# SELECT position( ' ' in version() ) ; > position > -- >11 > (1 row) > > Although possibly what you really want is split_part(). > > regression=# select split_part(version(), ' ', 2); > split_part > > 9.5.7 > (1 row) > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Exclude posts which was from blacklisted users Sql help
Hello,
Below is my table structure:
musedb_dev=# \d kudosposts
Table "public.kudosposts"
Column|Type |Modifiers
--+-+-
id | integer | not null default
nextval('kudosposts_id_seq'::regclass)
content | text|
user_id | integer |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
pix | character varying |
giphy_id | integer |
destroyed_at | timestamp without time zone |
TABLE "user_posts" CONSTRAINT "fk_rails_3b5b08eb72" FOREIGN KEY
(kudospost_id) REFERENCES kudosposts(id)
TABLE "comments" CONSTRAINT "fk_rails_bc8176e8bc" FOREIGN KEY
(kudospost_id) REFERENCES kudosposts(id)
musedb_dev=# \d user_posts
Table "public.user_posts"
Column|Type |Modifiers
--+-+-
id | integer | not null default
nextval('user_posts_id_seq'::regclass)
user_id | integer |
kudospost_id | integer |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
Foreign-key constraints:
"fk_rails_3b5b08eb72" FOREIGN KEY (kudospost_id) REFERENCES kudosposts(id)
"fk_rails_6c6a346128" FOREIGN KEY (user_id) REFERENCES users(id)
musedb_dev=# \d users
Table "public.users"
Column |Type |
Modifiers
+-+
id | integer | not null default
nextval('users_id_seq'::regclass)
email | character varying | not null default
''::character varying
black_list_user_ids| integer[] | default '{}'::integer[]
Referenced by:
TABLE "comments" CONSTRAINT "fk_rails_03de2dc08c" FOREIGN KEY (user_id)
REFERENCES users(id)
TABLE "settings" CONSTRAINT "fk_rails_5676777bf1" FOREIGN KEY (user_id)
REFERENCES users(id)
TABLE "user_posts" CONSTRAINT "fk_rails_6c6a346128" FOREIGN KEY (user_id)
REFERENCES users(id)
TABLE "kudosposts" CONSTRAINT "fk_rails_ba6b4c6f54" FOREIGN KEY (user_id)
REFERENCES users(id)
TABLE "favorites" CONSTRAINT "fk_rails_d15744e438" FOREIGN KEY (user_id)
REFERENCES users(id)
TABLE "user_kudos_milestones" CONSTRAINT "fk_rails_e5a78b2bce" FOREIGN KEY
(user_id) REFERENCES users(id)
musedb_dev=#
I am trying list posts whose owner is not in black lists users column of the
post receiver. But my sql still selecting the backlisted user posts.
Below is the SQL I tried:
SELECT
"kudosposts".*
FROM
"kudosposts"
INNER JOIN
"user_posts" "user_posts_kudosposts_join"
ON "user_posts_kudosposts_join"."kudospost_id" = "kudosposts"."id"
INNER JOIN
"users"
ON "users"."id" = "user_posts_kudosposts_join"."user_id"
AND "users"."destroyed_at" IS NULL
INNER JOIN
"user_posts"
ON "kudosposts"."id" = "user_posts"."kudospost_id"
WHERE
"kudosposts"."destroyed_at" IS NULL
AND "user_posts"."user_id" = 5
AND
(
kudosposts.user_id != all (users.black_list_user_ids)
)
ORDER BY
"kudosposts"."created_at” DESC
Could you help me to find out where I am wrong?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump not dropping event trigger
I have a DB with an event trigger, which I am dumping with pg_dump -Fc --no-acl --no-owner [db_name] > [dump_file] In my dump file, I can't find any DROP EVENT TRIGGER statement. In src/bin/pg_dump/pg_dump.c, dumpEventTrigger() calls ArchiveEntry() with a "" argument for the dropStmt parameter. Is it intended to create an entry with no DROP statement? To be more precise, my scenario is the following: * I wish to backup & restore a DB which contains an event trigger * I can't drop the DB and re-create it before restoring * I can't have CREATE EVENT TRIGGER throw errors about the trigger already existing What is the recommended procedure? -- 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] pg_dump not dropping event trigger
Greg Atkins writes: > I have a DB with an event trigger, which I am dumping with > pg_dump -Fc --no-acl --no-owner [db_name] > [dump_file] > In my dump file, I can't find any DROP EVENT TRIGGER statement. Well, you wouldn't, for lack of -c ... but yes, it seems you don't get a DROP EVENT TRIGGER even with that. Will fix. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
