EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE
measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' ORDER BY
measurement_time DESC LIMIT 1;
This seems to fail, scanning all tables. Do you think this can be
improved
at all ? The query plan of the above query is as
Hello all,
I have a problem with growing WAL-files populating a temporary table.
After running the following script 8192 times (each in separate connection)
I can see 3*16 MB WAL files.
CREATE TEMP TABLE IF NOT EXISTS positiontemporarytable (pos_instrument_id
integer, pos_code varchar(40));
Hi,
@Kyotaro HORIGUCHI
thanks for your reply and time Kyotaro,
Not at all.
Using the following query
EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE
measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' AND
measurement_time = '2015-01-01 00:00:00+0' LIMIT 1;
produces
I am migrating a DB from SQL Server to Postgres 9.2.7 on Centos 7, via
regex converting the SQL Server DDL to a Postgres DDL. Both DB's need
to be supported in the near term.
The biggest problem has been the tiny int boolean that SQL Server
uses, which I can get to work for postgres inserts by:
On 1/22/2015 10:22 AM, Igal @ getRailo.org wrote:
hi all,
I'm trying to follow the pg_upgrade docs from
http://www.postgresql.org/docs/9.4/static/pgupgrade.html
I'm on Windows 64bit, and experience a few issues:
1) I do not have a postgres user account. the services are run by the
On Thu, Jan 22, 2015 at 10:22:34AM -0800, Igal @ getRailo.org wrote:
Performing Consistency Checks
-
Checking cluster versions ok
*failure*
Consult the last few lines of pg_upgrade_server_start.log or
pg_upgrade_server.log for
On Thu, Jan 22, 2015 at 10:30:44AM -0800, Igal @ getRailo.org wrote:
could not connect to old postmaster started with the command:
C:\Program Files\PostgreSQL\9.3\bin/pg_ctl -w -l pg_upgrade_server.log
-D E:\PGSQLData -o -p 50432 -b start
any help would be appreciated.
thanks!
Bruce,
On 1/22/2015 10:38 AM, Bruce Momjian wrote:
On Thu, Jan 22, 2015 at 10:30:44AM -0800, Igal @ getRailo.org wrote:
pg_upgrade_server_start.log contains the following:
command: C:\Program Files\PostgreSQL\9.3\bin/pg_ctl -w -l
pg_upgrade_server.log -D E:\PGSQLData -o -p 50432 -b start
On Thu, Jan 22, 2015 at 10:46:50AM -0800, Igal @ getRailo.org wrote:
Bruce,
On 1/22/2015 10:38 AM, Bruce Momjian wrote:
On Thu, Jan 22, 2015 at 10:30:44AM -0800, Igal @ getRailo.org wrote:
pg_upgrade_server_start.log contains the following:
command: C:\Program
Did you try to use runas windows command? Should not make any difference based
on your comment how you run it.
https://technet.microsoft.com/en-us/library/cc771525(WS.10).aspx
On Thu, Jan 22, 2015 at 10:48 AM, Igal @ getRailo.org i...@getrailo.org
wrote:
Bruce,
On 1/22/2015 10:38 AM,
Jimmy,
On 1/22/2015 10:53 AM, Jimmy Jack wrote:
Did you try to use runas windows command? Should not make any
difference based on your comment how you run it.
https://technet.microsoft.com/en-us/library/cc771525(WS.10).aspx
runas what? there is no postgres user on my system. the Windows
Michael Paquier michael.paqu...@gmail.com writes:
On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko lizenk...@gmail.com wrote:
3. They are not WAL-logged.
Whats wrong with it in my case?
Nothing. Temporary tables are not WAL-logged, but transaction commit is.
Right. I think there is some
Hi Han,
Here is an example:
create table foo (v integer);
insert into foo values (23), (45), (65), (22);
create table bar (v integer);
insert into bar select v from foo, generate_series(1,5);
But note that in any relational database there is no defined order for
the rows. A table is more like a
Why unlogged tables behaviour is not the same?
If I try this:
create unlogged table if not exists positiontemporarytable
(pos_instrument_id integer, pos_code varchar(40));
prepare pos_delete as delete from positiontemporarytable;
execute pos_delete;
no WAL files created at all.
On 22
Hi
This table just has a column which type is integer. There are one million
data in this table. I wanna calculate standard deviation on each 50 data by
order. It means SD1 is from data 1 to data 50, SD2 is from data 51 to
100 Is there anyone who can give me some suggestions? Thanks
Pierre
On Thursday, January 22, 2015, tsunghan hsieh tsunghan.hs...@gmail.com
wrote:
Hi
I have a table which just has one column as following in Original Table. I
wanna duplicate all of data for few times and with same order as following
in New Table. Is there anyone who can help me? Thanks
Han
Hi Pierre,
How do you know in which group each row belongs? If you don't care how
the rows are grouped, you can say this:
create table foo (v float);
insert into foo select random() from generate_series(1, 100) s(a);
select n % 50 g, stddev(v) from (select row_number() over () n, v from
foo)
Are you sur you don't want a moving windows
(stddev on 0 to 50 , then stdev on 1 to 51)
..
If you don't want moving windows your query would look like
DROP TABLE IF EXISTS your_data;
CREATE TABLE your_data AS
SELECT s as gid , random() as your_data_value
FROM generate_series(1,1) as s ;
Pierre Hsieh wrote
Hi
This table just has a column which type is integer. There are one million
data in this table. I wanna calculate standard deviation on each 50 data
by
order. It means SD1 is from data 1 to data 50, SD2 is from data 51 to
100 Is there anyone who can give me some
On Thu, Jan 22, 2015 at 11:06 PM, Andrey Lizenko lizenk...@gmail.com wrote:
3. They are not WAL-logged.
Whats wrong with it in my case?
Nothing. Temporary tables are not WAL-logged, but transaction commit is.
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
Pierre Hsieh wrote
Hi,
Is there anyone who can help me to create a specific table as following?
Thanks
Pierre
rule:
1. just one column which type is integer in table
2. this columns only has 1 and 2 for 50 times as following
1
2
1
2
1
2
1
2
.
use generate_series(...),
On 01/22/2015 06:54 AM, Pierre Hsieh wrote:
Hi,
Is there anyone who can help me to create a specific table as following?
Thanks
The commands you will need are here:
http://www.postgresql.org/docs/9.3/interactive/sql-commands.html
In particular:
CREATE TABLE
On Thu, Jan 22, 2015 at 11:59 PM, David G Johnston
david.g.johns...@gmail.com wrote:
Pierre Hsieh wrote
1. just one column which type is integer in table
2. this columns only has 1 and 2 for 50 times as following
use generate_series(...), the modulus operator (to determine even/odd via
%2),
On 01/22/2015 06:06 AM, Andrey Lizenko wrote:
Hello all,
I have a problem with growing WAL-files populating a temporary table.
After running the following script 8192 times (each in separate
connection) I can see 3*16 MB WAL files.
3 * 16 = 48MB
Say each row takes 10 bytes(an underestimate).
Hi,
Is there anyone who can help me to create a specific table as following?
Thanks
Pierre
rule:
1. just one column which type is integer in table
2. this columns only has 1 and 2 for 50 times as following
1
2
1
2
1
2
1
2
.
On 01/22/2015 02:31 AM, robertlazarski . wrote:
I am migrating a DB from SQL Server to Postgres 9.2.7 on Centos 7, via
regex converting the SQL Server DDL to a Postgres DDL. Both DB's need
to be supported in the near term.
The biggest problem has been the tiny int boolean that SQL Server
uses,
robertlazarski . robertlazar...@gmail.com writes:
The biggest problem has been the tiny int boolean that SQL Server
uses, which I can get to work for postgres inserts by:
atdev=# update pg_cast set castcontext = 'a' where castsource =
'int'::regtype and casttarget = 'bool'::regtype;
You
Spiros Ioannou siv...@inaccess.com writes:
It is ORDER BY measurement_time, not measurement_id, and measurement_time
is used to create the partition. So the planner should know the correct
order, but instead it seems to query tables in the wrong order.
The planner does not know that, and even
On 23 January 2015 at 08:22, agent ziph...@gmail.com wrote:
Hi Craig I have a similar issue with a rather small number of servers
involved.
OK. That looks odd.
What revision are you running exactly?
git rev-parse --short HEAD
please.
Also, correponding logs from the other two nodes
ok, I resolved this by giving Full Permissions to the data directory.
I don't understand why this problem occurred in the first place.
probably something in the process that creates the Data directory.
thank you all for your help.
Igal
On 1/22/2015 5:46 PM, Igal @ getRailo.org wrote:
so
On Thu, Jan 22, 2015 at 05:46:08PM -0800, Igal @ getRailo.org wrote:
so when I try to run pg_ctl start I get this now:
c:\Program Files\PostgreSQL\9.3\binpg_ctl --pgdata=E:\PGSQLData start
server starting
c:\Program Files\PostgreSQL\9.3\bin2015-01-22 17:25:22 PST PANIC: could not
open
On Thu, Jan 22, 2015 at 07:13:40PM -0800, Igal @ getRailo.org wrote:
ok, I resolved this by giving Full Permissions to the data directory.
I don't understand why this problem occurred in the first place. probably
something in the process that creates the Data directory.
thank you all for
On Thu, Jan 22, 2015 at 12:25 PM, Tom Lane t...@sss.pgh.pa.us wrote:
You realize of course that you've set that to be an assignment cast,
not an implicit cast as the title of your message suggests. So this
only changes the behavior for assignment contexts, ie INSERT/UPDATE
target values.
so when I try to run pg_ctl start I get this now:
c:\Program Files\PostgreSQL\9.3\binpg_ctl --pgdata=E:\PGSQLData start
server starting
c:\Program Files\PostgreSQL\9.3\bin2015-01-22 17:25:22 PST *PANIC:
could not open control file global/pg_control: Permission denied*
so looks like that is the
maybe we can isolate the issue by breaking it into steps. this seems to
cause the problem:
C:\Program Files\PostgreSQL\9.4\binC:\Program
Files\PostgreSQL\9.3\bin/pg_ctl -w -l pg_upgrade_ctl.log -D
E:\PGSQLData -o -p 50432 -b start
waiting for server to startAccess is denied.
stopped
On 1/22/2015 6:54 AM, Pierre Hsieh wrote:
1. just one column which type is integer in table
2. this columns only has 1 and 2 for 50 times as following
note that tables are unordered sets, the rows of a table have no implied
order.1 1 1 1 1 2 2 2 2 2 is the same table as 1 2 1 2 1 2 1 2 1
Hi, not so long ago, I've asked myself the same question. I've written two
fuction that do this:
SELECT build_json(array_agg(to_json(old.key)), array_agg(old.value))
FROM json_each($1) old
LEFT OUTER JOIN json_each($2) new ON old.key = new.key
WHERE old.value::text new.value::text OR new.key
runas administrator
https://msdn.microsoft.com/en-us/library/bb385791.aspx
The new security model does not grant administrative privileges at all times.
Even administrators run under standard privileges when they perform
non-administrative tasks that do not require elevated privileges…
same result :(
C:\Windows\system32C:\Program Files\PostgreSQL\9.3\bin/pg_ctl -w -l
c:\pg_upgrade_ctl.log -D E:\PGSQLData -o -p 50432 -b start
waiting for server to startAccess is denied.
stopped waiting
pg_ctl: could not start server
Examine the log output.
can't find any log output
Hey all. I have a trigger function which does a delta of two hstore values
just doing a - b; this check is performed to see if there's a delta and if
not I don't log it.
I'm wondering if there's a suitable method for comparison two json objects?
I don't have 9.4 yet so I can't use jsonb, but if
Hi
it should to work for JSON too
http://8kb.co.uk/blog/2015/01/16/wanting-for-a-hstore-style-delete-operator-in-jsonb/
Regards
Pavel
2015-01-22 20:37 GMT+01:00 Wells Oliver wellsoli...@gmail.com:
Hey all. I have a trigger function which does a delta of two hstore values
just doing a - b;
It means, that 8192 commits + 8192 create temp table (and drop it after
closing connection) costs me 48 MB of WAL files.
And there is no way to reduce disk space usage, right?
Does amount of data which has to be written to WAL-file depend on size of
transaction?
On 22 January 2015 at 18:44, Tom
Hi. I am hoping that someone can advise me on an issue I am having with
connecting to a postgresql DB via unixODBC.
I am using an ubuntu VM to test the configuration. Here’s what I did:
1. Installed unixODBC
2. Installed odbc-postgresql
3. Configured odbc drivers and data sources.
I
On 01/22/2015 08:35 AM, Julie Reier wrote:
Hi. I am hoping that someone can advise me on an issue I am having with
connecting to a postgresql DB via unixODBC.
I am using an ubuntu VM to test the configuration. Here’s what I did:
1. Installed unixODBC
2. Installed odbc-postgresql
3.
hi all,
I'm trying to follow the pg_upgrade docs from
http://www.postgresql.org/docs/9.4/static/pgupgrade.html
I'm on Windows 64bit, and experience a few issues:
1) I do not have a postgres user account. the services are run by the
Network Service account.
2) minor: the service names include
Hi
I have a table which just has one column as following in Original Table. I
wanna duplicate all of data for few times and with same order as following
in New Table. Is there anyone who can help me? Thanks
Han
Original Table
23
45
65
22
New Table
23
23
23
45
45
45
65
65
65
65
22
22
22
22
Hi CraigI have a similar issue with a rather small number of servers
involved.*Node 1:*max_replication_slots = 4max_wal_senders = 6wal_level =
'logical'track_commit_timestamp = onshared_preload_libraries =
'bdr'max_worker_processes = 10log_error_verbosity = verboselog_min_messages
=
I'm working on a 9.4 logical replication plugin, mostly for my own edification,
and have run into a limit of my knowledge: How can I retrieve the role (either
oid or textual name) associated with the operations my callbacks are receiving?
Apologies if it is staring me in the face and I've
Take a look at this explain
http://explain.depesz.com/s/TTRN
The final number of records is very small but PG is starting out with a
massive number of records and then filtering most of them out.
I don't want to really force pg to always use the same index because in
some cases this strategy
49 matches
Mail list logo
