Обсуждение: reading cvs logs with pgadmin queries
pgadmin uses multi line queries and copy from 'cvslogs' cannot read them.
Does anyone have a work around or other solution ?
On 09/19/2013 05:47 AM, Dave Cramer wrote:
> pgadmin uses multi line queries and copy from 'cvslogs' cannot read them.
>
> Does anyone have a work around or other solution ?
It worked here for me:
test=# INSERT INTO
big_int_test
VALUES
(6.7);
COPY postgres_log FROM
'/usr/local/pgsql/data/pg_log/postgresql-2013-09-19_070152.csv' WITH csv;
-[ RECORD 7 ]----------+-----------------------------------------------
log_time | 2013-09-19 07:04:42.422-07
user_name | aklaver
database_name | test
process_id | 8281
connection_from | [local]
session_id | 523b0401.2059
session_line_num | 4
command_tag | idle
session_start_time | 2013-09-19 07:02:41-07
virtual_transaction_id | 2/30
transaction_id | 0
error_severity | LOG
sql_state_code | 00000
message | statement: INSERT INTO
| big_int_test
| VALUES
| (6.7);
detail |
hint |
internal_query |
internal_query_pos |
context |
query |
query_pos |
location |
application_name | psql
What are the errors you are seeing?
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
--
Adrian Klaver
adrian.klaver@gmail.com
The errors are extra lines after .... with a query like this
2013-09-12 10:33:19.145 WST,"user","dbname",14581,"192.168.1.22:58840",523126d3.38f5,1,"SELECT",2013-09-12 10:28:35 WST,6/503023,0,ERROR,42P01,"relation ""dds_stores"" does not exist",,,,,,"WITH RECURSIVE recursive_stores(id, name) AS ( SELECT id, name FROM customer_store WHERE id IN (1, 280, 864, 2376) UNION ALL SELECT ss.id, ss.name FROM recursive_stores sss, customer_store ss WHERE sss.id = ss.parent_store_id ) SELECT to_char(i.timestamp, 'Mon-YY') AS ""Month Name"", s.name AS ""Target"", COUNT(DISTINCT i.id) / (SELECT COUNT(dds_stores.id) FROM dds_stores) AS ""Ticket Count"" FROM customer_store s INNER JOIN printdata_workstation w ON s.id = w.store_id AND s.id IN (SELECT recursive_stores.id FROM recursive_stores) INNER JOIN printdata_report r ON w.id = r.workstation_id AND r.package_id IS NOT NULL INNER JOIN printdata_page p ON r.id = p.report_id INNER JOIN printdata_item i ON p.id = i.page_id WHERE r.timestamp >= '2012-09-01' AND r.timestamp <= '2013-08-31' GROUP BY ""Month Name"", ""Target""",367,,"pgAdmin III - Query Tool"
On Thu, Sep 19, 2013 at 10:14 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 09/19/2013 05:47 AM, Dave Cramer wrote:It worked here for me:pgadmin uses multi line queries and copy from 'cvslogs' cannot read them.
Does anyone have a work around or other solution ?
test=# INSERT INTO
big_int_test
VALUES
(6.7);
COPY postgres_log FROM '/usr/local/pgsql/data/pg_log/postgresql-2013-09-19_070152.csv' WITH csv;
-[ RECORD 7 ]----------+-----------------------------------------------
log_time | 2013-09-19 07:04:42.422-07
user_name | aklaver
database_name | test
process_id | 8281
connection_from | [local]
session_id | 523b0401.2059
session_line_num | 4
command_tag | idle
session_start_time | 2013-09-19 07:02:41-07
virtual_transaction_id | 2/30
transaction_id | 0
error_severity | LOG
sql_state_code | 00000
message | statement: INSERT INTO
| big_int_test
| VALUES
| (6.7);
detail |
hint |
internal_query |
internal_query_pos |
context |
query |
query_pos |
location |
application_name | psql
What are the errors you are seeing?--
Adrian Klaver
adrian.klaver@gmail.com
On 09/19/2013 08:26 AM, Dave Cramer wrote: > The errors are extra lines after .... with a query like this > > 2013-09-12 10:33:19.145 WST,"user","dbname",14581,"192.168.1.22:58840 <http://192.168.1.22:58840>",523126d3.38f5,1,"SELECT",2013-09-12 > 10:28:35 WST,6/503023,0,ERROR,42P01,"relation ""dds_stores"" does not > exist",,,,,,"WITH RECURSIVE recursive_stores(id, name) AS ( I going to assume you mean after the ,,,,,, and are referring to the WITH query. Several questions: 1) Are you using the example table from the docs?: http://www.postgresql.org/docs/9.3/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG If not what is the table definition? 2) What is the actual error message you get when running COPY? -- Adrian Klaver adrian.klaver@gmail.com
Adrian,
1) yes I am using the example table
CREATE TABLE postgres_log ( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp with time zone, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text, detail text, hint text, internal_query text, internal_query_pos integer, context text, query text, query_pos integer, location text, application_name text, PRIMARY KEY (session_id, session_line_num) );
2) The exact error message is ERROR: extra data after last expected column
considering ~39000 lines go in before this line I am fairly certain it is the line.
Dave
On Thu, Sep 19, 2013 at 8:25 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 09/19/2013 08:26 AM, Dave Cramer wrote:The errors are extra lines after .... with a query like this2013-09-12 10:33:19.145 WST,"user","dbname",14581,"192.168.1.22:58840 <http://192.168.1.22:58840>",523126d3.38f5,1,"SELECT",2013-09-12
10:28:35 WST,6/503023,0,ERROR,42P01,"relation ""dds_stores"" does not
exist",,,,,,"WITH RECURSIVE recursive_stores(id, name) AS (
I going to assume you mean after the ,,,,,, and are referring to the WITH query.
Several questions:
1) Are you using the example table from the docs?:
http://www.postgresql.org/docs/9.3/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG
If not what is the table definition?
2) What is the actual error message you get when running COPY?
--
Adrian Klaver
adrian.klaver@gmail.com
On 09/19/2013 06:04 PM, Dave Cramer wrote: > Adrian, > > 1) yes I am using the example table > > > 2) The exact error message is ERROR: extra data after last expected column > > > considering ~39000 lines go in before this line I am fairly certain it is the line. I would tend to agree, I just can't see why. The number of values matches the number of fields. Its the end of the day, I will take another look in the morning with fresh eyes. > > > > Dave -- Adrian Klaver adrian.klaver@gmail.com
On 09/19/2013 06:04 PM, Dave Cramer wrote:
> Adrian,
>
> 2) The exact error message is ERROR: extra data after last expected column
>
>
> considering ~39000 lines go in before this line I am fairly certain it is the line.
New day, new start. I am not sure now that the line you showed is the
problem. I isolated that line in a file and did a COPY into postgres
log. Note, I changed the time zone because my installation did not
recognize WST:
test=# COPY postgres_log FROM '/home/postgres/pg_csv_test.csv' WITH csv;
COPY 1
test=# \x
Expanded display is on.
test=# SELECT * from postgres_log ;
-[ RECORD 1
]----------+----------------------------------------------------------------------------
log_time | 2013-09-12 10:33:19.145-07
user_name | user
database_name | dbname
process_id | 14581
connection_from | 192.168.1.22:58840
session_id | 523126d3.38f5
session_line_num | 1
command_tag | SELECT
session_start_time | 2013-09-12 10:28:35-07
virtual_transaction_id | 6/503023
transaction_id | 0
error_severity | ERROR
sql_state_code | 42P01
message | relation "dds_stores" does not exist
detail |
hint |
internal_query |
internal_query_pos |
context |
query | WITH RECURSIVE recursive_stores(id, name) AS (
| SELECT id, name FROM customer_store
WHERE id IN (1, 280, 864, 2376)
| UNION ALL
| SELECT ss.id, ss.name FROM
recursive_stores sss, customer_store
| ss WHERE sss.id = ss.parent_store_id
| ) SELECT
| to_char(i.timestamp, 'Mon-YY') AS
"Month Name",
| s.name AS "Target",
| COUNT(DISTINCT i.id) / (SELECT
COUNT(dds_stores.id) FROM
| dds_stores) AS "Ticket Count"
| FROM
| customer_store s
| INNER JOIN printdata_workstation w ON
s.id = w.store_id AND s.id
| IN (SELECT recursive_stores.id FROM
recursive_stores)
| INNER JOIN printdata_report r ON w.id
= r.workstation_id AND
| r.package_id IS NOT NULL
| INNER JOIN printdata_page p ON r.id =
p.report_id
| INNER JOIN printdata_item i ON p.id =
i.page_id
| WHERE
| r.timestamp >= '2012-09-01' AND
r.timestamp <= '2013-08-31'
| GROUP BY
| "Month Name",
| "Target"
query_pos | 367
location |
application_name | pgAdmin III - Query Tool
>
>
>
> Dave
--
Adrian Klaver
adrian.klaver@gmail.com
OK,
I have a little more information.
Yes, in isolation I can import these lines, however something happens after 69000 lines. These lines cause an error.
On Fri, Sep 20, 2013 at 9:59 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 09/19/2013 06:04 PM, Dave Cramer wrote:Adrian,2) The exact error message is ERROR: extra data after last expected column
considering ~39000 lines go in before this line I am fairly certain it is the line.
New day, new start. I am not sure now that the line you showed is the problem. I isolated that line in a file and did a COPY into postgres log. Note, I changed the time zone because my installation did not recognize WST:
test=# COPY postgres_log FROM '/home/postgres/pg_csv_test.csv' WITH csv;
COPY 1
test=# \x
Expanded display is on.
test=# SELECT * from postgres_log ;
-[ RECORD 1 ]----------+----------------------------------------------------------------------------
log_time | 2013-09-12 10:33:19.145-07
user_name | user
database_name | dbname
process_id | 14581
connection_from | 192.168.1.22:58840
session_id | 523126d3.38f5
session_line_num | 1
command_tag | SELECT
session_start_time | 2013-09-12 10:28:35-07
virtual_transaction_id | 6/503023
transaction_id | 0
error_severity | ERROR
sql_state_code | 42P01
message | relation "dds_stores" does not existquery | WITH RECURSIVE recursive_stores(id, name) AS (
detail |
hint |
internal_query |
internal_query_pos |
context |query_pos | 367
| SELECT id, name FROM customer_store WHERE id IN (1, 280, 864, 2376)
| UNION ALL
| SELECT ss.id, ss.name FROM recursive_stores sss, customer_store
| ss WHERE sss.id = ss.parent_store_id
| ) SELECT
| to_char(i.timestamp, 'Mon-YY') AS "Month Name",
| s.name AS "Target",
| COUNT(DISTINCT i.id) / (SELECT COUNT(dds_stores.id) FROM
| dds_stores) AS "Ticket Count"
| FROM
| customer_store s
| INNER JOIN printdata_workstation w ON s.id = w.store_id AND s.id
| IN (SELECT recursive_stores.id FROM recursive_stores)
| INNER JOIN printdata_report r ON w.id = r.workstation_id AND
| r.package_id IS NOT NULL
| INNER JOIN printdata_page p ON r.id = p.report_id
| INNER JOIN printdata_item i ON p.id = i.page_id
| WHERE
| r.timestamp >= '2012-09-01' AND r.timestamp <= '2013-08-31'
| GROUP BY
| "Month Name",
| "Target"
location |
application_name | pgAdmin III - Query Tool
Dave
--
Adrian Klaver
adrian.klaver@gmail.com
Dave Cramer escribió: > OK, > > I have a little more information. > > Yes, in isolation I can import these lines, however something happens after > 69000 lines. These lines cause an error. Stray quotes in previous lines? Perhaps check whether line 65999 was imported correctly. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
I tried copying 500 lines above it and it still works :(
On Mon, Sep 23, 2013 at 4:15 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Dave Cramer escribió:> OK,Stray quotes in previous lines? Perhaps check whether line 65999 was
>
> I have a little more information.
>
> Yes, in isolation I can import these lines, however something happens after
> 69000 lines. These lines cause an error.
imported correctly.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 09/23/2013 12:46 PM, Dave Cramer wrote: > OK, > > I have a little more information. > > Yes, in isolation I can import these lines, however something happens > after 69000 lines. These lines cause an error. Is it the same error? The exact error message is ERROR: extra data after last expected column If so I would say the problem is in the transition between line 69000 and 69001. I wonder if you are getting bit by some variation of the below where partial lines are getting through in spite of the PK: http://www.postgresql.org/docs/9.3/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG "The table definition above includes a primary key specification. This is useful to protect against accidentally importing the same information twice. The COPY command commits all of the data it imports at one time, so any error will cause the entire import to fail. If you import a partial log file and later import the file again when it is complete, the primary key violation will cause the import to fail. Wait until the log is complete and closed before importing. This procedure will also protect against accidentally importing a partial line that hasn't been completely written, which would also cause COPY to fail." > > > > Dave Cramer > > -- Adrian Klaver adrian.klaver@gmail.com
Ok, I found the offending line. It was not the pgadmin line. There was a line with a large binary insert.
On Mon, Sep 23, 2013 at 6:31 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 09/23/2013 12:46 PM, Dave Cramer wrote:Is it the same error?OK,
I have a little more information.
Yes, in isolation I can import these lines, however something happens
after 69000 lines. These lines cause an error.If so I would say the problem is in the transition between line 69000 and 69001.
The exact error message is ERROR: extra data after last expected column
I wonder if you are getting bit by some variation of the below where partial lines are getting through in spite of the PK:
http://www.postgresql.org/docs/9.3/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG
"The table definition above includes a primary key specification. This is useful to protect against accidentally importing the same information twice. The COPY command commits all of the data it imports at one time, so any error will cause the entire import to fail. If you import a partial log file and later import the file again when it is complete, the primary key violation will cause the import to fail. Wait until the log is complete and closed before importing. This procedure will also protect against accidentally importing a partial line that hasn't been completely written, which would also cause COPY to fail."--
Dave Cramer
Adrian Klaver
adrian.klaver@gmail.com