17
SQL Server does not store UTF-8 under any circumstances. You get either UTF-16 Little Endian (LE) via NVARCHAR (including NCHAR and NTEXT, but don't ever use NTEXT) and XML, or some 8-bit encoding, based on a Code Page, via VARCHAR (including CHAR and TEXT, but don't ever use TEXT).
The problem here is that your code is mistranslating that 0x82 character, ...
15
The "Cannot assign requested address" part in the error message comes from the kernel TCP stack. When encountered intermittently, this typically means that the space of available sockets is exhausted because of too much sockets in wait state (TIME_WAIT, or less probably FIN_WAIT_1 or FIN_WAIT_2)
The range of socket ports can be output by cat /proc/sys/net/...
13
It's to do with Python's object model - there's always a way to get a reference to objects that could be unsafe. See the rexec module documentation and the restricted execution chapter of the docs for some info on the problems, as well as:
https://www.researchgate.net/publication/228612669_Controlling_access_to_resources_within_the_python_interpreter
http://...
12
Don't make your user transaction wait for the (hopefully!) successful completion of the Python script. Your entire transaction sits there and waits for this external process to run, try to send mail, etc. I doubt the e-mail really has to go out that instant - especially given you can't control any delays it has as it gets routed to the recipient's inbox ...
10
I find these multiple closings with age=0 worrying
No, this whole log looks fine and tends to show that pooling is working.
These entries with age=0 being tagged as LOG C, they concern communication between a client and pgBouncer. age=0 just means that the client used the connection for less than 1 second, which is consistent with the dates with ...
10
You can reverse the sort direction to get the minimum instead of the maximum value:
# Sort by myfield (ascending value) and return first document
collection.find_one(sort=[("myfield", 1)])["myfield"]
This example assumes that:
myfield is a numeric value (so the sort order makes sense to determine a minimum or maximum)
myfield exists in the matching ...
10
You can use the EXCLUDED keyword to access the values passed to INSERT. No need to pass them twice:
insert_sql = '''
INSERT INTO {t} (id,col1, col2, col3)
VALUES (%s, %s, NULLIF(%s, 'nan'), NULLIF(%s, 'nan'))
ON CONFLICT (id)
DO UPDATE SET
(col1, col2, col3)
= (EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3) ;
...
10
I performed the same steps and ran into the same problem. I found that the problem was resolved by removing the ~/.pgadmin directory (created during installation) and then re-running "python pgAdmin4.py"
9
with PGBouncer, you'd probably want to just stick with NullPool. In that case you may be able to share a single Engine across subprocesses since no socket connections will be carried over the subprocess boundary. But you can't share anything referring to a Connection object, like a Session with an active transaction, over this boundary. You definitely ...
9
psycopg2 is a wrapper around libpq, written in C, to expose a Python DB-API compatible API to Python programs. It implements Python objects in C that call libpq functions. It has a thin Python module wrapper around it to load it and provide some of the interface functionality that's easier to write in pure Python.
Anything that implements the DB-API to ...
8
There are two typical ways to express this.
With LIKE infix search:
SELECT title FROM snippets WHERE description LIKE '%evil%';
or with position:
SELECT title FROM snippets WHERE position('evil' in description) > 0;
Note that neither are indexable by default. Search for "infix search index" for more info on that, and look into pg_trgm if you need it.
7
Yes, it is possible to work with multiple databases at the same time but you're looking in the wrong place. psycopg2 is just a library that simplifies accessing and manipulating data coming out of PostgreSQL but it doesn't go far beyond what you can do with psql. What you're looking to do you can solve on the database level by using Foreign Data Wrappers.
...
6
You might be able to use the service broker, though that is probably overkill, as you state.
Alternately, if you don't want to install/manage an extra service for this one need, you could use xp_cmdshell or a CLR-based trigger to make an external call when needed, to a program that starts the desired process asynchronously (so, outside the transaction that ...
5
The "geospatial feature" of dynamodb isn't really anything else then calculating a geohash from the coordinates, storing the hash in the same row, create an index on the hash, and when querying based on a location, calculate the hash of that location and compare to the indexed hashes in the table. The algorithm of geohash is really simple and with a ...
5
Duplication isn't ideal, but in this case is probably the best choice. Set the table permissions so that the table owner is not the operational day to day database user your app runs as, and only GRANT your app the ability to write to the certificate data column, not the "cache" columns with expiry etc. Have a SECURITY DEFINER trigger function intercept ...
5
This is covered in the developer FAQ, some relevant excerpts:
MongoDB stores all field names in every document. For most documents,
this represents a small fraction of the space used by a document;
however, for small documents the field names may represent a
proportionally large amount of space
And, just a note on indexes:
Shorter field names do ...
5
I cannot see any possible way for this to work given that the script is being submitted through a back-end service where there is no user-context (i.e. display, etc). SSMS is not executing anything. It is merely submitting those statements to SQL Server which handles the execution (even if SQL Server then passes it off to something else).
The only potential ...
4
The package to install is postgresql-plpython3.
By way of dependencies, this will install postgresql-plpython3-9.1, which provides these files:
$ dpkg -L postgresql-plpython3-9.1
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/postgresql-plpython3-9.1
/usr/share/doc/postgresql-plpython3-9.1/copyright
/usr/share/postgresql
/usr/share/postgresql/9.1
/usr/...
4
Set the Application Name
If you expect to be running many processes you need to know where they are
connecting from. PGBouncer will make this invisible to pg_stat_activity. Solve
this by carefully setting the application_name with the information you'll
need:
# Sets the application name for this connection in the form of
# application-name:user@host
prog ...
4
I did some experimentation in console trying to figure out why this was happening for me as well. In the end, I searched right into the file folder for the package looking to ensure that the pooling file was present in the mysql.connector package version I had.
Sure enough there it was.
In the end, I simply changed my import statement. I had been using
...
4
Use the following parameter to set the number of maximum connections from your pymongo app.
client = MongoClient(host, port, maxPoolSize=10)
Here is documentation with more details.
4
I tried to fix this by submitting a patch #234 (approved but not yet committed). You'll have to edit the file ~/.local/bin/mssql-cli and replace
python -m mssqlcli.main "$@"
With this line
( command -v python3 && python3 -m mssqlcli.main "$@" ) || python -m mssqlcli.main "$@"
3
The fact that MySQL locks behave this way by default seems perverse to me, but that's not the point.
Actually, that's totally the point, because locks from SELECT statements is something MySQL doesn't ordinarily do... so by some as-yet-unknown mechanism, you've asked it to do that.
The most likely explanation is that you (or whatever you are using as a ...
3
DISCLAIMER : Not a Python Programmer
I can give you a generic algorithm
Let's say you have 4 DB Servers
replicator@10.1.2.% is the Replication User
1 Master (10.1.2.10)
3 Slaves (IP Addresses : 10.1.2.20,10.1.2.30,10.1.2.40)
STEP 01) Run this query
SELECT host FROM information_schema.processlist WHERE user = 'replicator';
Collect host columns into a ...
3
I addressed a similar issue in my blog Storing X.509 Digital Certificates (And Other Messy Things) and some earlier comments. (It's too long to cut & paste here.) Many of the points made here are much easier if you can create a user-defined function that extracts the fields you need to cache.
Addressing one other point above - it is possible to write a ...
3
A few patterns are evident in your data:
The primary activity is updates.
Updates come for several seconds after query/inserts.
The criteria fields in finds is indexed.
Even so, the 'index miss' rate is very high.
Background flush is very low (as it should be with SSD) so you're not getting I/O contention.
Lock-average is extremely high.
Based on this, I'm ...
3
PostgreSQL calls "schemas" what MySQL calls databases. Do not create multiple databases with the intention of them working together. That said, if you need to access an external database, the PostgreSQL FDW will help you out
PostgreSQL FDW
3
create materialized view foo as
select distinct machine_code, date(datetime) from thing;
create unique index on foo(machine_code,date);
Beware that the conversion of datetime to date will occur in the timezone of whoever last created or refreshed the MV. You might want to use this instead:
create materialized view foo as
select distinct ...
3
Welcome to DBA Stack Exchange!
My problem is that I don't know the 'speed' of SQL when it deals with
stats and maths, compared to other language. I know that basic
functions (correlation, R^2, ...) are already implemented in SQL, but
I am using far more 'advanced' (I mean 'complex' here..) functions
(even if I have not represented it here).
As a ...
3
I am not familiar with Python, but for your current requirement (i.e. backup).
I'd strongly recommend you to use PowerShell for two reasons:
MS in its SQLPS module (since SQL Server 2012), has a native cmdlet called
Backup-SQLDatabase
You may run this cmdlet on one central server to do the backup against multiple sql instances at once because this Backup-...
Only top voted, non community-wiki answers of a minimum length are eligible
