Hot answers tagged postgresql
4
This won't work. Don't even try feeding mysqldump output directly into psql. You'll need to dump schema and data separately, convert the schema either by hand or with a tool, load the converted schema into PostgreSQL then load the dumped data.
mysqldump's compatibility flags are moderately useful for dumping data but pretty useless for dumping schema ...
4
If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would:
Select the (relatively few) surviving rows into a temporary table.
Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer:
Optimizing bulk update ...
4
You can get some of that information by turning on the configuration parameter log_planner_stats. Most of that information, however, doesn't really exist, because the planner does not fully compute all alternative plans and their costs. It only explores an alternative plan until it can determine that it is slower than the current best plan. So alternative ...
3
time with time zone stores microseconds (8 bytes) and the time zone (4 bytes). timestamp with time zone stores just the microseconds and converts the time zone at display time. Because of the conceptual weirdness of the time with time zone type, the time zone needs to be stored explicitly. You don't actually need 8 bytes to store the number of ...
2
It sounds like what you probably want is to:
Create a role to own all the common tables and schema, or just use your own if you really will always be the only one with full control of the main tables.
Create another role you intend to give only read-only access to the shared tables and schemas. GRANT that role rights using GRANT SELECT ON ALL TABLES IN ...
2
I am not entirely sure how much this information helps, but the system table pg_stats contains a correlation column.
From the manual
Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper ...
2
I have experience this exact same thing in the past. It usually happens because you have linked psql against an inferior readline-like library. If you are using libedit, use libreadline instead. If you are using libreadline, use a different one. Details depend on your operating system and method of installation.
2
I don't think there is a global parameter to tweak in pgAdmin 1.16.*
As an alternative you can open tables with:
Tools -> View Data -> View Top 100 rows
This is also available from the context menu of a table in the object browser.
Once the data grid is open, a "Limit Bar" is available, where you can set a maximum for returned rows.
I would ...
2
You seem to be misinterpreting a part of my advice to your previous question:
so I can not Truncate and rename the temp_tables since the system
should be on all the time.
There was no renaming involved. After TRUNCATE you run an INSERT. The only blocking operation is the TRUNCATE. I quote the manual:
TRUNCATE acquires an ACCESS EXCLUSIVE lock on ...
2
I can suggest you another approach/solution. You can partition the table and delete partition older than 3 months just dropping them. This is fast and does not impact other inserts.
However seems that you need to keep at least one value; can you calculate values to be kept and insert them in another table?
1
No, there's no detailed optimizer audit/reporting structure or function.
Your best tool is 'gdb'. Unlike MS SQL Server, PostgreSQL is open source; you can just build with debug symbols enabled, attach a debugger, and trace exactly what it's doing.
That doesn't mean it's easy, of course. A canned set of breakpoints and gdb macros to produce a trace of what ...
1
There are no permissions on LISTEN and NOTIFY. It would not have made sense to have any until support for notify payloads was introduced in more recent versions.
If you want access control, create a table with the information that you want and then send a NOTIFY that's empty or has nothing but a primary key for that table in it. SELECT the row(s) of ...
1
This appears to work - for public. I'm assuming (but have not yet tested) that I can do something similar with another group role. As me (the local admin), I run the following.
create schema foo;
grant usage on schema foo to public;
alter default privileges in schema foo grant select on all tables to public;
select 1 into foo.test;
Once this has finished, ...
1
I found this answer:
http://stackoverflow.com/questions/10763143/in-rails-couldnt-create-database-for-adapter-postgresql
And, as simple as it was, it worked for me... ran a $bundle update and it started working again.
Only top voted, non community-wiki answers of a minimum length are eligible
