Re: [GENERAL] inserting a text file via json
Did you mean something like...?:
postgres=# \! echo {\menu\: { \id\: \file\ }}
/opt/pgdata/exampl.json
postgres=# select pg_read_file('/opt/pgdata/exampl.json');
pg_read_file
{menu: { id: file }}+
(1 row)
postgres=# select pg_read_file('/opt/bdr/bdr1/exampl.json')::json;
pg_read_file
{menu: { id: file }}+
(1 row)
postgres=# create table json_exam as select
pg_read_file('/opt/bdr/bdr1/exampl.json')::json as jsonColumn;
SELECT 1
postgres=# \d json_exam
Table public.json_exam
Column | Type | Modifiers
+--+---
jsoncolumn | json |
2014-09-05 20:51 GMT-03:00 john.tiger john.tigernas...@gmail.com:
we want to store markdown text files into our json fields - can this be
done ? I guess we could have a separate json field and bytea field for the
markdown file but this might be difficult when it comes to our REST
response - anyone do something like this ?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
--
Emanuel Calvo http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
Re: [GENERAL] Querying a time range across multiple partitions
Thanks Jeff! That's what I wanted to confirm, that I need to hard code /
pregenerate my dates in the query. I was mainly curious why it didn't work
with current_date, and that answers it.
And BTW, all my inserts happen on the most recent table, so my insert
trigger with a linear search is ordered date descending. It seems to work
fine since my use case is to insert data once, and never change it again.
Of course my only problem is when a select query confuses the planner, and
searches my entire set. ;-)
Thanks,
--Cal
On Sun, Sep 7, 2014 at 2:44 PM, Jeff Janes jeff.ja...@gmail.com wrote:
On Fri, Sep 5, 2014 at 10:31 AM, Cal Heldenbrand c...@fbsdata.com wrote:
explain analyze select time,event from logins
where username='bob' and hash='1234' and time current_date - interval
'1 week';
Result (cost=0.00..765.11 rows=1582 width=14)
- Append (cost=0.00..765.11 rows=1582 width=14)
- Seq Scan on logins (cost=0.00..0.00 rows=1 width=66)
Filter: (((username)::text = 'bob'::text) AND ((hash)::text
= '1234'::text) AND (time (('now'::text)::date - '7 days'::interval)))
- Index Scan using logins_20100501_username_time on
logins_20100501 logins (cost=0.01..0.48 rows=1 width=14)
...
This shows that it's attempting to run the query against all of my 1500
child tables.
I believe the problem is that the planner (which does the partition
pruning) is not willing to materialize the value of current_date, so it
can't use a specific value to prune partitions. After all, the date might
change between the planner and the executor, if you leave the plan open for
a long time, or make the call very close to midnight.
You will probably have to select the current_date (or get it from your
system, or cron, or whoever triggers the script), and then hardcode it into
the query.
Cheers,
Jeff
[GENERAL] Introducing Open PostgreSQL Monitoring (OPM)
# Introducing Open PostgreSQL Monitoring (OPM) ## Oversee and Manage Your PostgreSQL Servers _Paris, September 8th 2014_ The OPM Development Group (see below) is proud to present the first public release of Open PostgreSQL Monitoring, a fully open source monitoring suite for PostgreSQL. ## Get Graphs and Stats from your PostgreSQL instances! PostgreSQL may be the most advanced database in the world but one of its biggest handicap is the lack of open source dedicated monitoring tools. Compared to its competitors, the PostgreSQL community has not been able to produce tools similar to Oracle Grid Control, MySQL Workbench or SQL Server Management Studio. Today we'd like to present the OPM project: a fully open-source suite designed to provide dynamic graphs, custom stats series, performance analysis, server health overview and team management... If you have serveral postgres servers in production, Open PostgreSQL Monitoring is the perfect to watch the current activity of your databases, with DBA-centric stats such as: database size, hit ratio, streaming replication lag, system load, page bloat, longest queries, WAL files generation, locks, backends status, etc. ## Extensibility is the key This initial public version is obviously a first step toward more features and more versatility. OPM includes remote agents, a stat storage system and a web GUI. Most of the code is currently in Perl and the stat collection is based on Nagios, but the whole suite is designed to be extended to other monitoring frameworks and other languages. It is possible to your own remote agent, a specific stat storage or even an alternative user interface. The OPM project was started in 2012 and it's been used in production for months, monitoring dozens of servers like a charm. We're publishing today the version 2.3 with the hope that it will be useful for other PostgreSQL users around the world. ## Credits The OPM Development Group would like to thank all the developers who contributed to this release, especially Sebastien Fauveau from [Art is Code](http://a.rtisco.de/) for his UI/UX design skills. OPM is an open project available under the PostgreSQL License. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly to cont...@opm.io ## Links * Website : [http://opm.io/](http://opm.io/) * Demo : [http://demo.opm.io](http://demo.opm.io) (login/pass = opm/demo) * twitter : [@__opm__](http://twiter.com/__opm__) -- **About OPM** : Open PostgreSQL Monitoring is a free software designed to help you manage your PostgreSQL servers. It's a flexible tool that will follow the activity of each instance. It can gather stats, display dashboards and send warnings when something goes wrong. The long-term goal of the project is to provide similar features to those of Oracle Grid Control or SQL Server Management Studio. Code Demo at [http://opm.io/](http://opm.io/) -- **About the OPM Development Group** : This project is driven by the OPM Development Group (OPMDG), a team of developers gathered to build efficient monitoring tools for PostgreSQL. So far the main sponsor of this project is DALIBO, the leading PostgreSQL company in France. However the project team is open to ideas and contributions: individuals and companies who share the goals of the OPMDG are welcome to join the team! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to restore backup database
Hi, I use PostrgrSQL 9.3 for couple of months now, on Windows. I installed new Window OS, and before installing, I made tar backups of my PostgreSQL databases. Now I want to restore these on my new OS, but I can't find such option in pgAdmin. In documentation (http://www.postgresql.org/docs/9.3/static/backup.html) I see only suggestions how to restore sql dumps with command line tools, but none how to restore single file tar backups. Please suggest the right way to restore my backup databases. Thanks
Re: [GENERAL] How to restore backup database
On 09/08/2014 09:04 AM, klo uo wrote: Hi, I use PostrgrSQL 9.3 for couple of months now, on Windows. I installed new Window OS, and before installing, I made tar backups of my PostgreSQL databases. What was the command you used? Now I want to restore these on my new OS, but I can't find such option in pgAdmin. In documentation (http://www.postgresql.org/docs/9.3/static/backup.html) I see only suggestions how to restore sql dumps with command line tools, but none how to restore single file tar backups. Please suggest the right way to restore my backup databases. Well that depends on the answer to the previous question. Thanks -- 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] How to restore backup database
Hi Adrian, I used pgAdmin backup command. However in the meantime I figured how to restore. I first have to create database, and then use restore option which becomes available if I right-click on a database. On MSSQL for example, I can restore database without creating database first, and I guess I was expecting similar behavior. Cheers On Mon, Sep 8, 2014 at 6:47 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 09/08/2014 09:04 AM, klo uo wrote: Hi, I use PostrgrSQL 9.3 for couple of months now, on Windows. I installed new Window OS, and before installing, I made tar backups of my PostgreSQL databases. What was the command you used? Now I want to restore these on my new OS, but I can't find such option in pgAdmin. In documentation (http://www.postgresql.org/docs/9.3/static/backup.html) I see only suggestions how to restore sql dumps with command line tools, but none how to restore single file tar backups. Please suggest the right way to restore my backup databases. Well that depends on the answer to the previous question. Thanks -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] Async IO HTTP server frontend for PostgreSQL
Dear community,
I need a %subj% -- high performance HTTP server solution
based on asynchronous IO with ability to run PostgreSQL's
functions from HTML templates asynchronously and passing
the results to the HTTP client.
For example, consider a simple template:
html
div id=rows
${get_rows(id := :id)}
/div
/html
The function get_rows() will be called asynchronously
during the dispatching HTTP request and the result of
it will streams immediately to the HTTP client via async IO.
Currently, I've found only a module for NGINX
https://github.com/FRiCKLE/ngx_postgres
but it does not what I need.
Ideally, it should be a simple C (libevent based) or C++
(boost::asio based) library.
Please, if anyone has a ready solution of the idea described
above, let me know, because I don't want waste my time to write
it from scratch.
--
// Dmitriy.
Re: [GENERAL] How to restore backup database
On 09/08/2014 10:49 AM, klo uo wrote: Hi Adrian, I used pgAdmin backup command. However in the meantime I figured how to restore. I first have to create database, and then use restore option which becomes available if I right-click on a database. On MSSQL for example, I can restore database without creating database first, and I guess I was expecting similar behavior. You do not have to create a database in Postgres either. There are some system databases already created for you, template0(read only best left alone), template1 and postgres. You can connect to one of these(best practices, use postgres) and then have the restore script create the new database.IMPORTANT If you do this you need to check the Include CREATE DATABASE statement box (http://www.pgadmin.org/docs/1.18/restore.html) IMPORTANT To get a better idea of what is going on I would suggest reading the following sections of the docs: http://www.postgresql.org/docs/9.3/interactive/app-pgdump.html http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html This is what pgAdmin is using behind the scenes. Cheers -- 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] psql and tab-delimited output
On Sun, Sep 7, 2014 at 12:28 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Sat, Sep 6, 2014 at 12:32 AM, Abelard Hoffman abelardhoff...@gmail.com wrote: [snip] I know that COPY() will escape tabs (as \t), and we can use that from psql with the \copy command, but that does not include a header row of the column names. Which is a shame. \copy really should allow HEADER in the default format, not just CSV format. And it on the to-do list, just hasn't be done yet: https://wiki.postgresql.org/wiki/Todo#COPY It seems like it should be fairly easy to do. Ah, excellent. That would solve most of my issue. So, my question is, what's the simplest way to generate tab-escaped TSV-formatted reports with the first line containing the list of column names? I just assume that none of the column names need escaping, and so select and join them on tabs. At one point I had a perl script that would do this for me, e.g. given a query, it would execute it once with a 'and 1=0' at the end (obviously can't be done legally/efficiently/safely with all queries) to get the column names, then again in a \COPY to get the data, but I seem to have misplaced it. It worked well as long as you understood it was a dirty hack and so had the limitations of one. Yes, that's exactly what I need, although I'm loathe to run the queries twice just to get the headers. Thanks to everyone for their comments and suggestions. As additional background, I have a dozen or so reports that exist as plain text files, and they're just run by cron with psql and mailed to those who are interested in them. Historically, all of them have been TSV. It's worked fine since all of the data has been computed (e.g., sales counts, etc.). But with a recent report, we're including a customer feedback comment, which obviously can have tabs and newlines, etc. On Sun, Sep 7, 2014 at 9:25 AM, Adrian Klaver adrian.kla...@aklaver.com wrote: I also took a look at the psql source. It doesn't look like it would be very hard to add some backslash escaping logic as an option. Am I the only one that would use that? For reports, everyone else mostly uses other tools? I'd like to stay away from GUI-tools, if possible. Not sure what you are asking for here. Something like this?: http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html I was meaning, it seems like it would be useful to have psql be able to behave like COPY but without having to call \copy. So IOW, something like: psql --no-align --field-separator '\t' --field-escape Where the --field-escape option would cause the data to be escaped exactly like COPY does. Having the HEADER option to COPY's default format will largely solve this though. Then it's just a matter of slurping in the report files, making sure all newlines are removed, and passing them to the appropriate \copy command.
Re: [GENERAL] psql and tab-delimited output
Hi Alban. On Sun, Sep 7, 2014 at 4:18 AM, Alban Hertroys haram...@gmail.com wrote: On 07 Sep 2014, at 10:45, Abelard Hoffman abelardhoff...@gmail.com wrote: For reports, everyone else mostly uses other tools? I'd like to stay away from GUI-tools, if possible. For reporting, usually you use the data in the database directly. A TSV or CSV file is not a report, it’s at best a data source for your report. Going through an intermediate format is not a particularly effective approach to create reports, but if you have to (for example because you aren’t _allowed_ access to the database), generally preferred formats seem to be CSV, XML or JSON; as long as it’s a standard format. TSV is not a common choice. Are you sure your boss actually cares that it’s TSV and not, for example, CSV? Could you expand on that a bit? What sort of tools does management use to generate reports from the database directly? You're meaning a database warehouse? We just have an OLTP db, so we've always generated reports periodically through cron jobs. Or maybe reports is the wrong word. We generate a bunch of db stats which can then be used however they want (pulled into Excel, etc.). But would definitely be interested in learning about other approaches. And yes, I'm sure we could convert everything over to CSV. Just an issue of inertia. Thanks.
Re: [GENERAL] How to restore backup database
On Mon, Sep 8, 2014 at 8:10 PM, Adrian Klaver wrote: You do not have to create a database in Postgres either. There are some system databases already created for you, template0(read only best left alone), template1 and postgres. You can connect to one of these(best practices, use postgres) and then have the restore script create the new database.IMPORTANT If you do this you need to check the Include CREATE DATABASE statement box (http://www.pgadmin.org/docs/1.18/restore.html) IMPORTANT To get a better idea of what is going on I would suggest reading the following sections of the docs: http://www.postgresql.org/docs/9.3/interactive/app-pgdump.html http://www.postgresql.org/docs/9.3/interactive/app-pgrestore.html This is what pgAdmin is using behind the scenes. Nice. Thanks for the tip and suggestions. Cheers
[GENERAL] Last active time for a database
Is there a way for a superuser to find the last time a database had an active user connection? (While being logged into a different database in the same instance, of course). The context here is looking for looking for automated integration testing databases that have been leaked due to bugs/crashes in the testing framework. Yes, I can mine the server log files, but that is really scraping the bottom of the barrel. Thanks Jeff
Re: [GENERAL] Querying a time range across multiple partitions
John, On Fri, Sep 5, 2014 at 2:17 PM, John R Pierce pie...@hogranch.com wrote: On 9/5/2014 10:31 AM, Cal Heldenbrand wrote: Number of child tables: 1581 that's an insane number of children. We try and limit it to 50 or so child tables, for instance, 6 months retention by week, of data will millions of rows/day. I've used more than that many for testing purposes, and there was little problem. The main thing is that your insert trigger (if you have one on the master table) needs to be structured as a binary search-like nesting of if..elsif, not a linear-searching like structure. Unless of course almost all inserts go into the newest partition, then it might make more sense to do the linear search with that being the first test. But for performance, better to just insert directly into the correct child table. any select that can't be preplanned to a specific child will need to check all 1500 children. this is far less efficient than checking, say, 50 and letting the b-tree index of each child reject or narrow down to the specific row(s). The one is roughly 1500*log(N/1500) while the other is 50*log(N/50) at least to a first order approximation. can you explain that further? In the end, that argument sounds like it would always be more efficient to use a single table and its index instead, rather than partitioning it (log(N) c*log(N/c) for any c 1, if I'm not totally lost today). So, is there any insights of how many partitions are still useful? I have tables with roughly 1000 partitions and did not have any issues so far. Even with having INSERT rules that are lined up worst-case (from past to current, while data is always being inserted for the current date), I haven't seen any considerable degradation of INSERT performance so far. Thanks, Andreas -- 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] Querying a time range across multiple partitions
On 9/8/2014 1:40 PM, Andreas Brandl wrote: can you explain that further? In the end, that argument sounds like it would always be more efficient to use a single table and its index instead, rather than partitioning it (log(N) c*log(N/c) for any c 1, if I'm not totally lost today). it indeed would. good reasons for partitioning include... * efficient date based bulk deletion (we have a very large table that has 6 months retention, so we partition by week and delete the oldest week when a new week starts... dropping a partition is far faster than deleting 20 million records by date) * needing to put data across several tablespaces - I haven't had to do this. * more efficient vacuuming - really really large tables, like 100 GB, take a LONG time to vacuum. sane sized partitions will vacuum in less time, and since older time-based partitions aren't typically updated, they can be frozen. -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Issue with to_timestamp function
I've imported a csv export of an MS SQL Server log file into a staging table on
my local install of Postgresql (9.3/UTF8 encoding) for analysis.
The staging table definition is:
CREATE TABLE sql_log_import
(
id serial NOT NULL,
ts text, -- will convert to ts when merging into sql_server_logs
source character varying(30),
severity character varying(20),
message text,
CONSTRAINT sql_log_import_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE sql_log_import
OWNER TO postgres;
COMMENT ON COLUMN sql_log_import.ts IS 'will convert to ts when merging into
sql_server_logs';
Here's a copy of the first few lines of the data imported to table
sql_log_import:
08/06/2014 03:08:58,spid4s,Unknown,SQL Trace was stopped due to server
shutdown. Trace ID = '1'. This is an informational message only; no user action
is required.
08/06/2014 03:08:58,spid12s,Unknown,The current event was not reported to the
Windows Events log. Operating system error = 1717(The interface is unknown.).
You may need to clear the Windows Events log if it is full.
08/06/2014 03:08:58,spid12s,Unknown,Error: 17054c/ Severity: 16c/ State: 1.
08/06/2014 03:08:58,Server,Unknown,The connection has been lost with Microsoft
Distributed Transaction Coordinator (MS DTC). Recovery of any in-doubt
distributed transactions involving Microsoft Distributed Transaction
Coordinator (MS DTC) will begin once the connection is re-established. This is
an informational message only. No user action is required.
08/06/2014 03:08:58,spid12s,Unknown,Service Broker manager has shut down.
08/06/2014 03:08:55,Server,Unknown,SQL Server is terminating because of a
system shutdown. This is an informational message only. No user action is
required.
The final table is very similar, but with a timestamp with timezone field for
the logged server data. But, when I try to populate the target table with data
from the staging table, I keep getting an error. The issue is associated with
the to_timestamp function.
Here's what I'm seeing: If I use to_timestamp with the text data (copied from
table sql_log_import.ts), the select statement returns a timestamp with
timezone, as expected:
-- Executing query:
select to_timestamp('08/06/2014 03:08:58', 'MM/DD/ hh24:mi:ss')::timestamp
with time zone as tstamp
Total query runtime: 78 ms.
1 row retrieved.
But, when I select data from the table sql_log_import, I get an error:
-- Executing query:
select to_timestamp(ts, 'MM/DD/ hh24:mi:ss')::timestamp with time zone as
tStamp
from sql_log_import
where id = 10
** Error **
SQL state: 22007
Detail: Value must be an integer.
Any Ideas?
Thanks
Lou O'Quin
Re: [GENERAL] Async IO HTTP server frontend for PostgreSQL
On Mon, Sep 8, 2014 at 12:59 PM, Dmitriy Igrishin dmit...@gmail.com wrote:
Dear community,
I need a %subj% -- high performance HTTP server solution
based on asynchronous IO with ability to run PostgreSQL's
functions from HTML templates asynchronously and passing
the results to the HTTP client.
For example, consider a simple template:
html
div id=rows
${get_rows(id := :id)}
/div
/html
The function get_rows() will be called asynchronously
during the dispatching HTTP request and the result of
it will streams immediately to the HTTP client via async IO.
Currently, I've found only a module for NGINX
https://github.com/FRiCKLE/ngx_postgres
but it does not what I need.
Ideally, it should be a simple C (libevent based) or C++
(boost::asio based) library.
Please, if anyone has a ready solution of the idea described
above, let me know, because I don't want waste my time to write
it from scratch.
It's not in C, but you should take a very good look at node.js.
merlin
--
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] Issue with to_timestamp function
I suspect your data is not what you think it is.
What do you see when you do
SELECT ts FROM from sql_log_import LIMIT 3;
On Mon, Sep 8, 2014 at 4:52 PM, Lou Oquin loq...@nammotalley.com wrote:
I’ve imported a csv export of an MS SQL Server log file into a staging
table on my local install of Postgresql (9.3/UTF8 encoding) for analysis.
The staging table definition is:
CREATE TABLE sql_log_import
(
id serial NOT NULL,
ts text, -- will convert to ts when merging into sql_server_logs
source character varying(30),
severity character varying(20),
message text,
CONSTRAINT sql_log_import_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE sql_log_import
OWNER TO postgres;
COMMENT ON COLUMN sql_log_import.ts IS 'will convert to ts when merging
into sql_server_logs';
Here’s a copy of the first few lines of the data imported to table
sql_log_import:
08/06/2014 03:08:58,spid4s,Unknown,SQL Trace was stopped due to server
shutdown. Trace ID = '1'. This is an informational message only; no user
action is required.
08/06/2014 03:08:58,spid12s,Unknown,The current event was not reported to
the Windows Events log. Operating system error = 1717(The interface is
unknown.). You may need to clear the Windows Events log if it is full.
08/06/2014 03:08:58,spid12s,Unknown,Error: 17054c/ Severity: 16c/
State: 1.
08/06/2014 03:08:58,Server,Unknown,The connection has been lost with
Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of any
in-doubt distributed transactions involving Microsoft Distributed
Transaction Coordinator (MS DTC) will begin once the connection is
re-established. This is an informational message only. No user action is
required.
08/06/2014 03:08:58,spid12s,Unknown,Service Broker manager has shut down.
08/06/2014 03:08:55,Server,Unknown,SQL Server is terminating because of a
system shutdown. This is an informational message only. No user action is
required.
The final table is very similar, but with a timestamp with timezone field
for the logged server data. But, when I try to populate the target table
with data from the staging table, I keep getting an error. The issue is
associated with the to_timestamp function.
Here’s what I’m seeing: If I use to_timestamp with the text data (copied
from table sql_log_import.ts), the select statement returns a timestamp
with timezone, as expected:
-- Executing query:
select to_timestamp('08/06/2014 03:08:58', 'MM/DD/
hh24:mi:ss')::timestamp with time zone as tstamp
Total query runtime: 78 ms.
1 row retrieved.
But, when I select data from the table sql_log_import, I get an error:
-- Executing query:
select to_timestamp(ts, 'MM/DD/ hh24:mi:ss')::timestamp with time zone
as tStamp
from sql_log_import
where id = 10
** Error **
SQL state: 22007
Detail: Value must be an integer.
Any Ideas?
Thanks
*Lou O’Quin*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Re: [GENERAL] Issue with to_timestamp function
Lou Oquin loq...@nammotalley.com writes:
Ive imported a csv export of an MS SQL Server log file into a staging table
on my local install of Postgresql (9.3/UTF8 encoding) for analysis.
The staging table definition is:
CREATE TABLE sql_log_import
(
id serial NOT NULL,
ts text, -- will convert to ts when merging into sql_server_logs
source character varying(30),
severity character varying(20),
message text,
CONSTRAINT sql_log_import_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE sql_log_import
OWNER TO postgres;
COMMENT ON COLUMN sql_log_import.ts IS 'will convert to ts when merging into
sql_server_logs';
Heres a copy of the first few lines of the data imported to table
sql_log_import:
08/06/2014 03:08:58,spid4s,Unknown,SQL Trace was stopped due to server
shutdown. Trace ID = '1'. This is an informational message only; no user
action is required.
08/06/2014 03:08:58,spid12s,Unknown,The current event was not reported to the
Windows Events log. Operating system error = 1717(The interface is unknown.).
You may need
to clear the Windows Events log if it is full.
08/06/2014 03:08:58,spid12s,Unknown,Error: 17054c/ Severity: 16c/ State:
1.
08/06/2014 03:08:58,Server,Unknown,The connection has been lost with
Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of any
in-doubt distributed
transactions involving Microsoft Distributed Transaction Coordinator (MS DTC)
will begin once the connection is re-established. This is an informational
message only.
No user action is required.
08/06/2014 03:08:58,spid12s,Unknown,Service Broker manager has shut down.
08/06/2014 03:08:55,Server,Unknown,SQL Server is terminating because of a
system shutdown. This is an informational message only. No user action is
required.
The final table is very similar, but with a timestamp with timezone field for
the logged server data. But, when I try to populate the target table with
data from the
staging table, I keep getting an error. The issue is associated with the
to_timestamp function.
Ok but why not you just cast since the input data is compatible anyway,
at least from what I saw up there...
sj$ psql -efq --no-psqlrc
begin;
BEGIN
create temp table foo as
select '08/06/2014 03:08:58'::text as ts;
SELECT 1
Table pg_temp_7.foo
Column | Type | Modifiers
+--+---
ts | text |
select ts::timestamptz
from foo;
ts
2014-08-06 03:08:58-05
(1 row)
sj$
Heres what Im seeing: If I use to_timestamp with the text data (copied
from table sql_log_import.ts), the select statement returns a timestamp with
timezone, as
expected:
-- Executing query:
select to_timestamp('08/06/2014 03:08:58', 'MM/DD/
hh24:mi:ss')::timestamp with time zone as tstamp
Total query runtime: 78 ms.
1 row retrieved.
But, when I select data from the table sql_log_import, I get an error:
-- Executing query:
select to_timestamp(ts, 'MM/DD/ hh24:mi:ss')::timestamp with time zone as
tStamp
from sql_log_import
where id = 10
** Error **
SQL state: 22007
Detail: Value must be an integer.
Any Ideas?
Thanks
Lou OQuin
--
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
[GENERAL] Crash in 9.4 Beta when partially collapsing left outer joins
I think this is the first time I've ever reported a PG crash, which is notable since I've been using PG for over 10 years. ;) Using the 9.4 Beta RPMs on CentOS 6.X/64, we're experiencing a reproducible crash when running a query with a left outer join, partially collapsed. TRAP: FailedAssertion(!(!restriction_is_or_clause((RestrictInfo *) orarg)), File: indxpath.c, Line: 1213) 2014-09-08 14:21:33.179 PDT LOG: server process (PID 19957) was terminated by signal 6: Aborted 2014-09-08 14:21:33.179 PDT DETAIL: Failed process was running: SELECT students.id FROM students LEFT OUTER JOIN enrollments ON ( enrollments.students_id = students.id ) WHERE ( students.id = 5008 OR ( ( students.birthcity = 'Chico' OR students.birthcity IS NULL ) AND enrollments.start 20141219 ) ); 2014-09-08 14:21:33.179 PDT LOG: terminating any other active server processes 2014-09-08 14:21:33.179 PDT WARNING: terminating connection because of crash of another server process 2014-09-08 14:21:33.179 PDT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. Here's the smallest query I could find that it would crash on. Run on a blank database, the following will reliably crash postgres: CREATE TABLE students (id SERIAL PRIMARY KEY, birthcity VARCHAR DEFAULT NULL); CREATE TABLE enrollments (students_id INTEGER NOT NULL REFERENCES students(id), start INTEGER); SELECT students.id FROM students LEFT OUTER JOIN enrollments ON ( enrollments.students_id = students.id ) WHERE ( students.id = 5008 OR ( ( students.birthcity = 'Chico' OR students.birthcity IS NULL ) AND enrollments.start 20141219 ) ); --- Other environment stuff: [root@db1 pgsql]# rpm -qa | grep postg postgresql94-libs-9.4beta2-1PGDG.rhel6.x86_64 postgresql94-server-9.4beta2-1PGDG.rhel6.x86_64 postgresql94-devel-9.4beta2-1PGDG.rhel6.x86_64 postgresql92-libs-9.2.9-1PGDG.rhel6.x86_64 postgresql94-9.4beta2-1PGDG.rhel6.x86_64 postgresql94-contrib-9.4beta2-1PGDG.rhel6.x86_64 [root@db1 pgsql]# uname -a Linux db1.schoolpathways.com 2.6.32-431.23.3.el6.x86_64 #1 SMP Thu Jul 31 17:20:51 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux What other information should I provide? We have the machine available if necessary. Benjamin Smith -- 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] Querying a time range across multiple partitions
On Mon, Sep 8, 2014 at 1:40 PM, Andreas Brandl m...@3.141592654.de wrote: John, On Fri, Sep 5, 2014 at 2:17 PM, John R Pierce pie...@hogranch.com wrote: On 9/5/2014 10:31 AM, Cal Heldenbrand wrote: Number of child tables: 1581 that's an insane number of children. We try and limit it to 50 or so child tables, for instance, 6 months retention by week, of data will millions of rows/day. I've used more than that many for testing purposes, and there was little problem. The main thing is that your insert trigger (if you have one on the master table) needs to be structured as a binary search-like nesting of if..elsif, not a linear-searching like structure. Unless of course almost all inserts go into the newest partition, then it might make more sense to do the linear search with that being the first test. But for performance, better to just insert directly into the correct child table. any select that can't be preplanned to a specific child will need to check all 1500 children. this is far less efficient than checking, say, 50 and letting the b-tree index of each child reject or narrow down to the specific row(s). The one is roughly 1500*log(N/1500) while the other is 50*log(N/50) at least to a first order approximation. can you explain that further? In the end, that argument sounds like it would always be more efficient to use a single table and its index instead, rather than partitioning it (log(N) c*log(N/c) for any c 1, if I'm not totally lost today). Right. Partitioning is NOT a generic way to improve query performance. With rare exceptions, partitioning makes query performance worse, and then partition pruning may or may not serve to recover most of that lost performance. Partitioning allows you conspire with PostgreSQL to micromanage the the layout of the data in ways that improve manageability and maintainability. For example, you pay a little overhead each time you run a query and (perhaps) each time you insert a row, but in exchange for that you can delete a year of data with a nearly-instantaneous command. Cheers, Jeff
Re: [GENERAL] psql and tab-delimited output
On Sun, Sep 7, 2014 at 5:17 PM, Melvin Davidson melvin6...@gmail.com wrote: the output I'd prefer is: id fld_1 1 test\tvalue 2 test\tvalue 3 test\tvalue *Does this work for you?copy (SELECT id, replace(fld_1, '', '\t') FROM tsv_test) to stdout with csv header delimiter '';* Thank you, Melvin. Yes, I think that's what I'll do for the time-being, until COPY's TSV format supports HEADER.
Re: [GENERAL] Issue with to_timestamp function
On 09/08/2014 01:52 PM, Lou Oquin wrote:
I’ve imported a csv export of an MS SQL Server log file into a staging
table on my local install of Postgresql (9.3/UTF8 encoding) for analysis.
The staging table definition is:
CREATE TABLE sql_log_import
(
id serial NOT NULL,
ts text, -- will convert to ts when merging into sql_server_logs
source character varying(30),
severity character varying(20),
message text,
CONSTRAINT sql_log_import_pk PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE sql_log_import
OWNER TO postgres;
COMMENT ON COLUMN sql_log_import.ts IS 'will convert to ts when merging
into sql_server_logs';
Here’s a copy of the first few lines of the data imported to table
sql_log_import:
08/06/2014 03:08:58,spid4s,Unknown,SQL Trace was stopped due to server
shutdown. Trace ID = '1'. This is an informational message only; no user
action is required.
08/06/2014 03:08:58,spid12s,Unknown,The current event was not reported
to the Windows Events log. Operating system error = 1717(The interface
is unknown.). You may need to clear the Windows Events log if it is full.
08/06/2014 03:08:58,spid12s,Unknown,Error: 17054c/ Severity: 16c/
State: 1.
08/06/2014 03:08:58,Server,Unknown,The connection has been lost with
Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of any
in-doubt distributed transactions involving Microsoft Distributed
Transaction Coordinator (MS DTC) will begin once the connection is
re-established. This is an informational message only. No user action is
required.
08/06/2014 03:08:58,spid12s,Unknown,Service Broker manager has shut down.
08/06/2014 03:08:55,Server,Unknown,SQL Server is terminating because of
a system shutdown. This is an informational message only. No user action
is required.
The final table is very similar, but with a timestamp with timezone
field for the logged server data. But, when I try to populate the
target table with data from the staging table, I keep getting an error.
The issue is associated with the to_timestamp function.
Here’s what I’m seeing: If I use to_timestamp with the text data
(copied from table sql_log_import.ts), the select statement returns a
timestamp with timezone, as expected:
-- Executing query:
select to_timestamp('08/06/2014 03:08:58', 'MM/DD/
hh24:mi:ss')::timestamp with time zone as tstamp
Total query runtime: 78 ms.
1 row retrieved.
But, when I select data from the table sql_log_import, I get an error:
-- Executing query:
select to_timestamp(ts, 'MM/DD/ hh24:mi:ss')::timestamp with time
zone as tStamp
from sql_log_import
where id = 10
** Error **
SQL state: 22007
Detail: Value must be an integer.
Any Ideas?
Where are you getting this error?
Or to put it another way, where are you executing the query?
Thanks
*Lou O’Quin*
--
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] Issue with to_timestamp function
The data is ts 08/06/2014 03:08:58 08/06/2014 03:08:58 08/06/2014 03:08:58 Hmmm, this works for me: CREATE TABLE sql_log_import ( id serial NOT NULL, ts text, -- will convert to ts when merging into sql_server_logs CONSTRAINT sql_log_import_pk PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); INSERT INTO sql_log_import VALUES (1, '08/06/2014 03:08:58'), (2, '08/06/2014 03:08:58'), (3, '08/06/2014 03:08:58') SELECT to_timestamp(ts, 'MM/DD/ hh24:mi:ss')::timestamp FROM sql_log_import; ts 2014-08-06 03:08:58 2014-08-06 03:08:58 2014-08-06 03:08:58 *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] Issue with to_timestamp function
On 09/08/2014 04:06 PM, Lou Oquin wrote: I'm executing the query in pgAdmin3, in a SQL query window. The results are coming from the history tab of the output pane. Alright. I was trying to clear up confusion on my end, because the log entries you show are coming from SQL Server. Do you have log data from Postgres that cover the errors? Also you mention- 'Here’s a copy of the first few lines of the data imported to table sql_log_import:', but show the SQL Server log. Could we see that data? What happens if you run the command from psql? Thanks Lou -- 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] Issue with to_timestamp function
On 09/08/2014 01:52 PM, Lou Oquin wrote:
I’ve imported a csv export of an MS SQL Server log file into a staging
table on my local install of Postgresql (9.3/UTF8 encoding) for analysis.
select to_timestamp(ts, 'MM/DD/ hh24:mi:ss')::timestamp with time
zone as tStamp
from sql_log_import
where id = 10
** Error **
SQL state: 22007
Detail: Value must be an integer.
Any Ideas?
To get that error I had to do something like this:
hplc= select to_timestamp('aug/06/2014 03:08:58 ', 'MM/DD/
hh24:mi:ss');
ERROR: invalid value au for MM
DETAIL: Value must be an integer.
So at a guess, the data being imported has some month abbreviations in it.
Thanks
*Lou O’Quin*
--
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] Crash in 9.4 Beta when partially collapsing left outer joins
li...@benjamindsmith.com writes: Using the 9.4 Beta RPMs on CentOS 6.X/64, we're experiencing a reproducible crash when running a query with a left outer join, partially collapsed. The test case crashes as described for me. Will take a look tomorrow. Thanks for the report! 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] Crash in 9.4 Beta when partially collapsing left outer joins
On Tue, Sep 9, 2014 at 6:36 AM, li...@benjamindsmith.com wrote: What other information should I provide? We have the machine available if necessary. This can be reproduced without especially LEFT OUTER JOIN, and system crashes as long as index path is taken in planner, and that WHERE clause uses a given combination of OR and AND like the one in the query given. Here is a more simple example: create table aa (a int); create index aai on aa(a); select a1.a from aa a1, aa a2 where a1.a = 0 or (a1.a = 0 or a1.a = 1) and a2.a = 0; Some bisecting is showing as well that the commit at the origin of the regression is f343a88. Regards, -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
