On Sun, Nov 15, 2020 at 9:39 AM Adrian Klaver
wrote:
> On 11/14/20 8:24 PM, David G. Johnston wrote:
> > On Fri, Nov 13, 2020 at 1:48 PM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > Which is an indication that for changes of this sco
On Sunday, November 15, 2020, Maksim Fomin wrote:
>
>
> ‐‐‐ Original Message ‐‐‐
> On Sunday, November 15, 2020 4:32 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
> On Sunday, November 15, 2020, Maksim Fomin wrote:
>
>> ‐‐‐ O
alyze_scale_factor=0.2}
>
>>
>>>
auto-vacuum doesn't care directly about absolute size, it cares about
change (relative to absolute size in many cases, hence the scale factors).
David J.
gt;
If it doesn't have value it wouldn't matter whether it would be problematic.
David J.
On Tue, Nov 17, 2020 at 3:12 PM Post Gresql wrote:
> Hello.
>
> It seems that I can not create a type with
>
> create type my_type as my_table%rowtype;
>
> or
>
> create t
cause positions
> could change like 0 could become 1, includes and excludes could change
> positions, allOf could be anyOf etc.
> Any idea of how to always select ids in includes no matter the changes?
>
Maybe it can be done using json path:
https://www.postgresql.org/docs/13/functions-json.html#FUNCTIONS-SQLJSON-PATH
David J.
r of which is unfortunately named "vacuum full".
If you haven't started learning/thinking about it yet you should try and
get an understanding around where your system is in the process of
requiring an anti-wraparound vacuum. Or, more generally, using "vacuum
freeze".
David J.
ge type" use case is thus rendered basically
undoable. And I don't see the effort to be worth the benefit for "create
type" alone.
Just write: create type my_type (a int, b int); -- b's type matches
my_table.my_column
And/Or: comment on column my_type.b is '@depends on my_table.my_column%type'
David J.
ata directory it is being pointed
to is from a different major version.
David J.
9.6 is able to run against the older
> > cluster (DB service starts, queries work, etc), and the data could be
>
> Hmm, missed that. As David said that should not happen and if you are
> running a new binary against an old cluster then you will get corruption.
>
>
Actually,
iscontinued version, onto one which has
a perfectly usable solution, then my interest in pondering a work-around is
near zero.
David J.
mption when making a blind suggestion. v12
would be the best from an efficiency/risk perspective at this moment in
time, IMO. v9.6 is only being supported for one more year would be the
reason to avoid choosing it.
David J.
s://www.postgresql.org/
> docs/13/sql-createfunction.html
> *"rettype*
>
> The return data type (optionally schema-qualified). The return type can be
> a base, composite, or domain type, or can reference the type of a table
> column."
>
>
The word “composite”. Every table has an associated composite type of the
same name.
David J.
was an error in "SELECT ..." and hence internally
> postgres executed ROLLBACK??
>
>
If there was an error you would see an error message. Plus, PostgreSQL
doesn’t just internally issue a rollback on its own. That said I’m not
sure what is being shown, or rather where the “execute” stuff comes from.
David J.
recovery for other reasons. This is why I always
> do the following:
>
Do any of those other reasons allow connections that could execute that
function to exist?
David J.
On Friday, November 20, 2020, Paul Förster wrote:
> Hi David,
>
> > On 20. Nov, 2020, at 10:34, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
> >
> >
> > On Friday, November 20, 2020, Paul Förster
> wrote:
> >
> >
properties of the database."
David J.
e (same county) to the right code?
>
>
>
IMO, don’t use triggers. Load the data into a temporary, or unlogged
table, and then run commands to do what you want against the live tables.
Truncate/drop before doing that again the following week.
David J.
On Saturday, November 21, 2020, Hagen Finley wrote:
> David,
>
> That's an interesting idea. I WOULD like to retain the OLD records that
> are the same and only INSERT new or changed records. Is there a way to
> compare the old and the new records without a trigger?
>
A where clause?
David J.
On Saturday, November 21, 2020, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Saturday, November 21, 2020, Hagen Finley wrote:
>
>> David,
>>
>> That's an interesting idea. I WOULD like to retain the OLD records that
>> are the same and onl
On Tue, Nov 24, 2020 at 7:36 AM David Gauthier
wrote:
> Hi:
>
> 11.3 on linux
>
> I've come up with a plan to archive data from my main DB which involves
> creating other DBs on the same server. But even though there will be zero
> activity on the archive DBs in ter
" or you can pass a probably easier to write "csv" value and
write "ANY(string_to_array(?, ','))".
David J.
values argument, not an
"array object" (or the equivalent in C). That said, if you can import a
client-side header that provides a helper function for this array-to-text
conversion (idk?) then passing the data becomes easier and you just need to
decide whether to add a cast in the SQL or provide the OID.
David J.
https://www.postgresql.org/docs/current/datatype-character.html
David J.
As its easy enough to run can you please confirm this by showing us the
pg_stat_activity record for that pid during the time when this warning
appears?
Thanks!
David J.
across, even for CRUD.
e.g.,
UPDATE remote_tbl
SET ...
FROM local_tbl
WHERE remote_tbl.col_id=local_tbl.col_id;
or
INSERT INTO remote_tbl SELECT * FROM local_tbl;
Not saying that there isn't room for improvement here but I'm doubting it's
simple.
David J.
;t dig into this too deeply but I did download the zip for 42.2.9 and
those classes don't even exist at that point since the build was re-done
using pre-processors.
David J.
cify -h /tmp in your commands
It might be interesting to see what “which psql” shows in each case; or
whether psql works in the sudo -u case.
David J.
in one mode was compiled using a different default than the server
on the same machine. From which we infer you have two client installs on
your machine, each compiled using different defaults.
David J.
On Mon, Nov 30, 2020 at 8:36 AM Nick Cleaton wrote:
> I want to set up a large table on postgresql 12.4, using declarative
> partitioning to partition by record creation date. I'd like to have recent
> records in small partitions but old records in a few larger partitions, so
> I want merges. The
easurement
>
>
psql? on (default) port 5432
> $ /usr/lib/postgresql/12/bin/pg_dump -p5433 -d postgres -t measurement
> --data-only
>
>
pg_dump on port 5433
Usually different ports means different clusters
David J.
’m unsure whether to expect a dump of only the partitioned table’s data to
be empty or include everything. I suspect “empty” is the correct answer.
If you dump everything the individual tables would be dumped, and not all
partition should be dumped.
David J.
quires the user to adhere to
a naming scheme. There is room for a long-form argument in a similar vein
to
--load-via-partition-root
to export through partition root.
David J.
On Wed, Dec 2, 2020 at 5:06 PM Ron wrote:
> On 12/2/20 5:35 PM, David G. Johnston wrote:
>
> On Wednesday, December 2, 2020, Ron wrote:
>
>> What am I missing?
>>
>> (Specifying the whole file name because multiple versions are installed.)
>>
>> $ /u
table to a separate schema and require that
all partitions are placed there as well. Otherwise, the necessary
information exists in the catalogs, so a solution is within reach (minor
concern regarding concurrency).
David J.
that
is ruled out (though here I did register the partitioned table aspect
eventually).
David J.
le and test against
the columns pf that instead of executing multiple queries.
David J.
it is
> definitely worth the effort.
>
Given that one can write this with a subquery without much difficulty i’m
doubtful that effort spent in this area is going to be particularly
valuable.
David J.
ay_elements" to navigate past the array and
get to the next layer of the json where ->>'Name' will then work.
For v12 and newer readers, SQL/JSON Path should probably be used instead.
David J.
On Mon, Dec 7, 2020 at 4:49 PM Ken Tanzer wrote:
>
>
> On Mon, Dec 7, 2020 at 3:22 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Mon, Dec 7, 2020 at 4:13 PM Steve Baldwin
>> wrote:
>>
>>> Try:
>>>
>>> selec
uot;\"r1kval\"","\"r1kval2\""}
> 2 | {"\"r2kval\"","\"r2kval2\""}
> (2 rows)
>
> I think the quotes are a fault of example data?
>
The quotes are the fault of the query author choosing the "->" operator
instead of "->>".
David J.
l
joining reduces nesting which is measurably cleaner.
David J.
going over my head?
>
That is the lateral join.
https://www.postgresql.org/docs/current/sql-select.html
Read the section under from, join, lateral.
David J.
ELECT ORDER BY", but it doesn't seem workable for the
driver to try and do that when adding the returning clause, which I presume
is what is in scope here.
David J.
eturning SELECT ORDER BY", but it doesn't seem workable for
> the driver to try and do that when adding the returning clause, which I
> presume is what is in scope here.
>
> David J.
>
> Thank you, it's appreciated! I'm sure this clarification would help
e SQL engine is complaining that it has no idea
what you want it to do.
David J.
On Sunday, December 13, 2020, Muthukumar.GK wrote:
> Hi David,
>
> As I am not bit Clea, let me know what I have to do. If possible, please
> re- write my program.
>
Like the SQL executor, I have no idea what you are trying to do there.
Neither the text variable, nor the cursor,
On Monday, December 14, 2020, David G. Johnston
wrote:
>
> On Sunday, December 13, 2020, Muthukumar.GK wrote:
>
>> Hi David,
>>
>> As I am not bit Clea, let me know what I have to do. If possible, please
>> re- write my program.
>>
>
> Like the SQL
t provides that feature.
David J.
Suggestions welcome if there is a better way to solve this problem.
>
>
As you are writing literals just put an array in the to-be-merged array.
select '["a","b"]'::jsonb || '[["c","d"]]'::jsonb
David J.
.' || table_name)::regclass) from
information_schema.tables;
David J.
hing within the trigger in that case.
David J.
On Tuesday, December 29, 2020, Susan Hurst
wrote:
>
> ##-- shell script command
> psql -c < ${CSVPATH}copycmd.z
>
Given the meaning of “-c” what are you expecting that to do?
David J.
ting of the written
description of the problem.
Or consider using less indirection until you get something that is working
and then add your layers incrementally testing as you go along.
David J.
quot;ALTER INDEX" documentation is more
pertinent. And behold:
ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] )
David J.
an equal number
of records per date, suggests to me that you are expecting the function to
fill in the blanks when the documentation says it doesn't do that.
David J.
On Thursday, January 7, 2021, Pavel Stehule wrote:
>
>
> The vulnerability is almost the same although it is a little bit harder to
> create attack strings.
>
Would making the function run as “security definer” and setting up a
minimal permissions user/owner help with mitigation?
David J.
-data. You get all this extra benefit at the cost of
not retaining the original text.
Admittedly, the system probably should be made to save the text, should
someone wish to write such a patch. Given the generally better-accepted
version control and migration management method of maintaining one's
database structure the need and desire to add such a capability to the core
server is quite low.
David J.
On Saturday, January 9, 2021, Alexander Farber
wrote:
>
> BEGIN
> FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT tiles FROM
> words_moves WHERE mid = in_mid)
>
>
Subqueries must be enclosed in parentheses. The parentheses that are part
of the function call do
em catalogs?
>
Views are relation-like and thus are primarily recorded on pg_class.
David J.
pi/verify_assertion.sql
>
The relevant function takes in a column argument - it is thus impossible
for the planner to evaluate the expression. And, as above, the planner
respects the "volatile" attribute of functions.
David J.
old your nose and adopt a path of least resistance - accept what is
easy to accomplish and be glad you aren't writing more user-friendly stuff,
but that is only cosmetically different, yourself. Learning what the
canonical outputs mean is annoying but not hard, especially if you do have
an original human-readable document to answer questions.
David J.
view, and then use the somewhat recent
multi-column statistics feature, there is probably room for improvement.
Otherwise, the question is basically an open-ended one and maybe someone
responds with some rules-of-thumb, or maybe they don't.
David J.
ng like this I'll usually just ignore since I know that answers
already exist to be found, and it is not something that I have chosen to
become a "librarian/interactive assistant" for - I relegate to the
documentation for that.
David J.
how the schema, query, and
explain results, hopefully both good and bad, and comment on how analyze
seems to affect the plan choice. But for the general question about
overcoming our statistics limitations the analyze point is not relevant.
David J.
r receiving a response, but I also don't feel bad when I
don't respond because of the lack, or when my response is to point people
to that already existing information.
David J.
rrective comments is a virtue of this community.
David J.
lity, which is what is being done here - not
providing excuses. We aren't apologetic for these realities, while
understanding that improvement is possible (if hard to achieve - technical
and personal).
David J.
olicies; and
also guidance and resources for “community defenders” on how constructively
engage when this kind of “meta” discussion arises. As I’m not volunteering
for the work this is just idle complaining and ideation on my part.
Oh, and an auto-responder instead of an FAQ...
David J.
providing support/tools
that can mitigate this human aspect of the request/reply dynamic.
David J.
;t working right;
the pgsql-admin list may contain both "pg" and "admin" in the title but
that is not its purpose.
David J.
tention while leaving alone ones I'm not
involved in.
In reverse setting up a rule to ignore emails sent both to the group and
myself is possible if I wish to treat any that hit the group the same and
know that the ones also addressed personally are duplicates to those others.
So reply-all gives people options while reply-to-list only doesn't.
Options are good.
David J.
by asking such
there is a chance for someone to decide to volunteer an improvement to the
documentation so that the same question doesn't have to be asked in the
future.
David J.
write a C language function if one wishes to implement a
custom range.
David J.
d but its can be
> cut off.
>
Seems like an insert on conflict would be simpler. Otherwise, what is your
question or concern?
David J.
field, not because the id value in a table is
actually null.
David J.
e one row and split it into
> constituent monthpieces and distribute amount proportionally.
>
> select * from func(1, 2021-01-04, 2021-02-06, 100);
> [...]
> How can I accomplish this, please.
>
Lateral Join.
(not tested)
SELECT *
FROM resultset, func(id, date_begin, date_end, amount);
David J.
> can give to my user to allow creation and dropping of partitions without
> allowing them to drop the parent table?
>
I doubt it...might want to consider writing a security definer function
that you can give them permission to run instead of having them do things
directly.
David J.
On Thursday, February 11, 2021, Steve Baldwin
wrote:
> Try ... EXECUTE PROCEDURE customer_num_informix()
>
FUNCTION, not PROCEDURE
David J.
On Thursday, February 11, 2021, Steve Baldwin
wrote:
> David, from what I can see of the docs, for 9.6 it is PROCEDURE. It seems
> FUNCTION didn't appear until 11.
>
Indeed. I didn’t pay attention to the version.
David J.
to make the
installation behave version-dependently. Within PostgreSQL itself that
would be most easily done by writing a pl/pgsql function that conditionally
adds the MATERIALIZED indicator on the textual representation of the query
before executing it. You can hide that function call within a view if
desired.
David J.
ostgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
David J.
atabase work?
>
>
It did - you just didn’t start a new session as the documentation directs.
You only altered a default that is only considered during user sign-in.
David J.
On Tue, Feb 16, 2021 at 11:47 AM Alexander Farber <
alexander.far...@gmail.com> wrote:
> Thank you for any hints
>
>
json_build_array(...)
David J.
specify some preference in pg_admin to guarantee this?
>
pgAdmin4 might be keying off of the presence of an index, which this table
doesn’t have.
David J.
On Tuesday, February 16, 2021, Alexander Farber
wrote:
>
> But is it possible in SQL to combine all 3 queries, so that a JSONB list
> of lists is returned?
> So I have to use PL/PgSQL, correct?
>
With liberal usage of CTEs and subqueries writing a single SQL query should
be doable.
David J.
execute the DO, capture an error if there is one,
rollback the transaction.
David J.
On Tue, Feb 16, 2021 at 4:28 PM Tim Cross wrote:
>
> David G. Johnston writes:
>
> > On Tue, Feb 16, 2021 at 3:43 PM Ron wrote:
> >
> >>
> >> How does one go about syntax checking this?
> >>
> >> (There are 222 ALTER TABLE ADD FORE
t’s called concatenation.
https://www.postgresql.org/docs/11/functions-json.html
David J.
On Sat, Feb 20, 2021 at 11:39 AM Alexander Farber <
alexander.far...@gmail.com> wrote:
>
> Or is the syntax error about being able to use JSONB_AGG only once per
> SELECT query?
>
>
That.
David J.
on in a select/group-by. array_agg(sum(...)) is
two aggregates in a single expression.
David J.
seem to be difficult to use.
>
>
With experience it just becomes verbose - at least for non-trivial cases.
David J.
a Unix-domain socket."
You will need to demonstrate that the connection you are checking from
isn't being made via a Unix-domain socket.
David J.
[ 155, 263 , ...]
> }
>
If you want the days aggregated then don’t “group by day”
David J.
ameter.
David J.
edure for me to migrate this database from 9
> to version 12 of the database?
>
>
https://www.postgresql.org/docs/current/pgupgrade.html
David J.
ion of our
license. My only gripe is that it fails to explicitly say "The PostgreSQL
License", per [1].
As the COPYRIGHT file exists at the top of the repo all source code
underneath, including the contrib directory, is covered.
[1] https://opensource.org/licenses/postgresql
David J.
what I can tell, would
doing so be a useful exercise. There may be some reason to inspect whether
a transaction has performed a write/update, but even that seems unlikely.
David J.
auto rset = my_exec(…)” is
doing is probably at fault.
David J.
e work
is done in the same transaction context as the local database.
David J.
m which
permanent data can be deleted.
See the following for ways to deal with duplicate removal on incorrectly
constrained tables.
https://harshitjain.home.blog/2019/06/17/postgresql-how-to-delete-duplicate-rows-in-postgresql/
David J.
1101 - 1200 of 2416 matches
Mail list logo
